Searching in a column that stores array of numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Searching in a column that stores array of numbers

Author

Topic

sixside
Starting Member

12 Posts

Posted - 2007-08-26 : 00:01:39

I have a Languages column that stores an array of numbers (the numbers correspond to spoken languages). Some example possible values for the column:

4
2, 5
1, 2, 11
2, 6, 9, 12

I need to come up with a query that I can search for matching integers in the Languages column. I tried using something like:

WHERE Languages LIKE '%2%'

But that will result in unexpected results since '2' shows up in '12' as well.

What's the best way to go about getting the accurate results I want?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-26 : 00:07:35

Your spaces might cause some issues though. If all your data follows a certain pattern then it might be a little easier.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-26 : 05:21:05

as Dinakar said, if the data has a certain pattern,

DECLARE @TABLE TABLE (Languages varchar(100))

INSERT INTO @TABLE
SELECT	'4'		UNION ALL
SELECT	'2, 5'		UNION ALL
SELECT	'1, 2, 11'	UNION ALL
SELECT	'3, 6, 9, 12'

SELECT	*
FROM	@TABLE
WHERE	' ' + Languages + ',' LIKE '% 2,%'
/*
Languages
---------
2, 5
1, 2, 11
*/

KH
[spoiler]Time is always against us[/spoiler]

sixside
Starting Member

12 Posts

Posted - 2007-08-26 : 05:31:54

Thanks khtan!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-27 : 02:11:14

See if you need
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

hankswart
Starting Member

5 Posts

Posted - 2007-08-28 : 11:59:10

Hi SixSide,

You could use the following:

DECLARE @TABLE TABLE (VALUE VARCHAR (3), AR VARCHAR (255))

INSERT INTO @TABLE VALUES ('AAA', '1 , 2 , 3')
INSERT INTO @TABLE VALUES ('BBB', '4 , 5 , 6')
INSERT INTO @TABLE VALUES ('CCC', '7 , 8 , 9')

--Get value to search for
DECLARE @SEARCH_STRING VARCHAR (255)
SELECT @SEARCH_STRING = '1'

-- Build a "LIKE" string with limiters of the array E.g. "%,1,%"
DECLARE @COMPLETE_SEARCH_STRING VARCHAR (255)
SELECT @COMPLETE_SEARCH_STRING = '%,' + @SEARCH_STRING + ',%'

-- Add a comma to the start and end of the original string to ensure that all individual values are enclosed in commas.
SELECT *, ',' + REPLACE(T.AR, ' ', '') + ',' FROM @TABLE T
WHERE
(',' + REPLACE(T.AR, ' ', '') + ',' LIKE @COMPLETE_SEARCH_STRING)

This isn't a very good way of doing it but it's a quick fix... I can think of a way to do it using a cursor but that wouldn't be ideal too... Rather upgrade to SQL 2005 and use the XML column features...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 12:05:57

I would consider not to store data in csv format

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources