Simple sql query format

Transact-SQL
1

I have a column in a table that contains the following list ... '1,3,5,7,9,11' ... how would I check to see if that column contains the value '1' ... select rowid from tablename where mycolumn ... contains/like ...1 ???

2

where ','+mycolumn+',' like '%,1,%'

3

That won’t work. It would match the 1 in 1 or the 1 in 11... I’m looking for something that takes into account that it is a list element.

4

I bet the time it took you replying would have taken longer time, than if you copy/pasted and pressed F5

1 Like
5

Actually - this will not match the 1 in 11 because the search includes the delimiters. Another way of writing this:

declare @searchValue varchar(2) = '1';
where concat(',', mycolumn, ',') like concat('%,', @searchValue, ',%')

This results in:

where ',1,3,5,7,9,11,' like '%,1,%'

Another way of performing this would be to use a string split utility:

SELECT rowid
FROM tablename t
CROSS APPLY dbo.DelimitedSplit8K(t.myColumn, ',') s
WHERE s.ItemValue = '1'

1 Like
6

what ?

7

Did you even attempt to test what he provided? Or - test what I provided? All 3 solutions work as expected - the one using the delimited split function will need that function though...if you don't have that function then you can find it with a google search.

1 Like
8

What he provided didn't work. Unfortunately, his solution does not take into account the search criteria in either the 1st or last position of the list.

Your solution was way over my head. In order to use it, I would first have to construct the function, and provided I had done that correctly, I could have tested it.

Thanks again for trying to help.

9

And - as I stated before - his solution does take into account the first and last position in the list. Again - adding a comma to the beginning and end of the string results in this:

,1,3,5,7,9,11,

Like '%,1,%'

This will not match on ,11, - it will only match on ,1,

1 Like
10

Hi

Hope this helps :slight_smile:

Please click arrow to the left for Sample Data

Sample Data 
drop table #SampleData
go 

create table #SampleData
(
col_id int ,
column_sample varchar(100)
)
go 

insert into #SampleData select 1,  '1,3,5,7,9,11'
insert into #SampleData select 2,  '3,5,7,9,11'
insert into #SampleData select 3,  '1,3'
go 

select 'Sample Data',* from #SampleData 
go

;WITH [cte]
AS (
       SELECT
            [t].[col_id]
            , LTRIM(RTRIM([m].[n].[value]('.[1]', 'varchar(8000)'))) AS [column_sample_split]
       FROM
        (   SELECT
                [#SampleData].[col_id]
                , CAST('<XMLRoot><RowData>' + REPLACE([#SampleData].[column_sample], ',', '</RowData><RowData>')
                       + '</RowData></XMLRoot>' AS XML) AS [x]
            FROM
                [#SampleData]) AS [t]
        CROSS APPLY [x].[nodes]('/XMLRoot/RowData') AS [m]([n])
   )
SELECT
    'SQL OutPut'
    , [cte].[col_id]
    , [cte].[column_sample_split]
FROM
    [cte]
WHERE
    [cte].[column_sample_split] = 1;

image

1 Like
11

Its a nice idea.
This expression will be appended to one of my handy files "useful_expressions.txt"