SELECTing WHERE string is within other string

[Sugapablo]

I have column data that looks like this:

id | ColumnA
---+-----------
1 | 12,35,123
2 | 1,23
3 | 233,34,35
4 | 34

And I want to be able to make a WHERE clause where I can match up a
value to one of the values between the commas. I.e. each of the numbers
between the commas are seperate values I wish to compare against.

Is there any function, perhaps like VBScripts "Split" function, where I
can split those numbers up and compare a value against them?

Perhaps there's another answer someone has?

Example:
If I'm searching with a value of "12" row 1 would be returned.
If I'm searching with a value of "35", row 1 and 3 would be returned.

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ su*******@12jabber.com <--jabber IM ]

[David Portas]

You're asking for a query solution to a problem caused by a flawed design.
Have you considered changing the design instead? This is much easier:

CREATE TABLE Sometable (id INTEGER, col_a INTEGER, PRIMARY KEY (id,col_a))

INSERT INTO Sometable VALUES (1,12)
INSERT INTO Sometable VALUES (1,35)
INSERT INTO Sometable VALUES (1,123)
INSERT INTO Sometable VALUES (2,1)
INSERT INTO Sometable VALUES (2,23)
INSERT INTO Sometable VALUES (3,233)
INSERT INTO Sometable VALUES (3,34)
INSERT INTO Sometable VALUES (3,35)
INSERT INTO Sometable VALUES (4,34)

SELECT id
FROM Sometable
WHERE col_a = 35

--
David Portas
------------
Please reply only to the newsgroup
--

[Simon Hayes]

"Sugapablo" <ru********@sugapablo.com> wrote in message
news:sl***********************@dell.sugapablo.net. ..

I have column data that looks like this:

id | ColumnA
---+-----------
1 | 12,35,123
2 | 1,23
3 | 233,34,35
4 | 34

And I want to be able to make a WHERE clause where I can match up a
value to one of the values between the commas. I.e. each of the numbers
between the commas are seperate values I wish to compare against.

Is there any function, perhaps like VBScripts "Split" function, where I
can split those numbers up and compare a value against them?

Perhaps there's another answer someone has?

Example:
If I'm searching with a value of "12" row 1 would be returned.
If I'm searching with a value of "35", row 1 and 3 would be returned.

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ su*******@12jabber.com <--jabber IM ]

There may well be a better way, but this should work (the string functions
in MSSQL are rather limited):

/* With a constant */

select *
from foo
where cola like '35,%' or
cola like '%,35,%' or
cola like '%,35'

/* With a variable */

declare @i int
set @i = 35

select *
from foo
where cola like cast(@i as varchar(5)) + ',%' or
cola like '%,' + cast(@i as varchar(5)) + ',%' or
cola like '%,' + cast(@i as varchar(5))

This will be inefficient on large data sets, though, and you may want to
look at your data model - lists in a single column can often be normalized.

Simon

[Rich Dillon]

As David pointed out, the design of your table makes this harder than it
needs to be. Best would be to change it, otherwise:

SELECT id, ColumnA
FROM tablename
WHERE ',' + ColumnA + ',' LIKE '%,35,%';

Hope that helps,
Rich
"Sugapablo" <ru********@sugapablo.com> wrote in message
news:sl***********************@dell.sugapablo.net. ..

I have column data that looks like this:

id | ColumnA
---+-----------
1 | 12,35,123
2 | 1,23
3 | 233,34,35
4 | 34

And I want to be able to make a WHERE clause where I can match up a
value to one of the values between the commas. I.e. each of the numbers
between the commas are seperate values I wish to compare against.

Is there any function, perhaps like VBScripts "Split" function, where I
can split those numbers up and compare a value against them?

Perhaps there's another answer someone has?

Example:
If I'm searching with a value of "12" row 1 would be returned.
If I'm searching with a value of "35", row 1 and 3 would be returned.

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ su*******@12jabber.com <--jabber IM ]

[Erland Sommarskog]

Sugapablo (ru********@sugapablo.com) writes:

I have column data that looks like this:

id | ColumnA
---+-----------
1 | 12,35,123
2 | 1,23
3 | 233,34,35
4 | 34

And I want to be able to make a WHERE clause where I can match up a
value to one of the values between the commas. I.e. each of the numbers
between the commas are seperate values I wish to compare against.

Is there any function, perhaps like VBScripts "Split" function, where I
can split those numbers up and compare a value against them?

As others have pointed out, this is not a good table design, and as a
collolary of this, there are not much functions in SQL Server to handle
this.

If you are stuck with this design, then you may get some ideas on
http://www.sommarskog.se/arrays-in-s...#unpack-tblcol, but you should
really consider the possibilities for a redesign.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp