SQL function to search across all columns in view for a string

Question

I have a table with 99 columns and a rowid:

[RowID] [int] NOT NULL,
[Col1] [nvarchar](max) NULL,
[Col2] [nvarchar](max) NULL,
[Col3] [nvarchar](max) NULL,
[Col4] [nvarchar](max) NULL,
.......
[Col99] [nvarchar](max) NULL

Table

I need a function that can be used in a Micrsoft SQL Server view (must be a view and no variables in the view) to search for a unique string and then display as a new field in the view. My current approach is using a case statement to search through each column until the unique string is found. This approach is cumbersome to write out (inefficient) and I have several unique strings to search for.

    SELECT     RowID, COALESCE (
    CASE WHEN Col1 LIKE '%UniqueString%' THEN Col1 ELSE NULL END, 
    CASE WHEN Col2 LIKE '%UniqueString%' THEN Col2 ELSE NULL END, 
    CASE WHEN Col3 LIKE '%UniqueString%' THEN Col3 ELSE NULL END, 
    CASE WHEN Col4 LIKE '%UniqueString%' THEN Col4 ELSE NULL END, 
    CASE WHEN Col5 LIKE '%UniqueString%' THEN Col5 ELSE NULL END, 
    CASE WHEN Col6 LIKE '%UniqueString%' THEN Col6 ELSE NULL END,  
    ………..
    CASE WHEN Col99 LIKE '%UniqueString%' THEN Col99 ELSE NULL END) AS [UniequeString] 
    FROM         dbo.TABLE_A

The end view should have the following result:

enter image description here

so the string could be only in one or zero column for each row? — Roman Pekar, Aug 08 '13 at 19:50
You have several unique strings to search for? Then what you do display in the output column UniqueString — paparazzo, Aug 08 '13 at 19:57

score 2 · Answer 1 · answered Aug 08 '13 at 19:56

In you example 'UNIQUESTRING' is the value of the column. If this is the case, then using like is overkill. You can do:

select (case when 'UNIQUESTRING' in (col1, col2, . . ., col99) then 'UNIQUESTRING'
        end)

Another possibility. It looks like the UNIQUESTRING is in the first non-NULL value for each column. If so, you can do:

select (case when coalesce(col1, col2, . . . col99) like '%UNIQUESTRING%'
             then coalesce(col1, col2, . . . col99)
        end)

@user1783736 . . . These formulations do not make that assumption. Each returns the first column that might have the value. — Gordon Linoff, Aug 08 '13 at 20:22

score 1 · Answer 2 · edited May 23 '17 at 12:05

If you have more than 10 columns, you don't want to specify it explicitly. Instead, use Columns to Rows XML trick:

;with cte1 as (
  select t.RowID, (select t.* for xml raw('row'), type) as Data
  from Temp1 as t
), cte2 as (
    select
         C.RowID,
         F.C.value('local-name(.)', 'nvarchar(128)') as Name,
         F.C.value('.', 'nvarchar(max)') as Value
    from cte1 as c
        outer apply c.Data.nodes('row/@*') as F(C)
)
select
    RowID,
    max(
        case
            when Name = 'RowID' then null
            when Value like '%uniquestring%' then Value
        end
    ) as Value
from cte2
group by RowID

sql fiddle example

SQL function to search across all columns in view for a string

2 Answers2