SQL Server: check if specific string exists in column

There is user created table type:

CREATE TYPE [dbo].[tpSearchCriteria] AS TABLE(
    [TableName] [sysname] NOT NULL,
    [ColumnName] [sysname] NOT NULL,
    [ValueString] [nvarchar](max) NULL,
    [ValueNumeric] [decimal](19, 6) NULL,
    [ValueDate] [datetimeoffset](7) NULL,
    [Operator] [nvarchar](2) NULL,
    [OperatorGroup] [smallint] NOT NULL,
    [SortDirection] [nvarchar](2) NULL )

Every time when query is executed this table is populated with some search parameters. In dependance of string values in column [ColumnName] I should chose different collation. So, there is need to examine in [ColumnName] does it contain values like 'FirstName' or 'LastName'

I tried with some CASE and IF clauses but that is not working.

System using SQL Server 2014