sql query complex

Question

I have table where in a table called test which have 4 fields.one field named as listing, I have 1,2,3,4,5,6 multiple values separated by comma, I need to check whether in that table and in that particular field an id say 4 is there or not.. by a sql query.

Answer 1

Select * From Foo Where Col like '%4%' ??

Answer 2

If you need to find 4 and only 4 (ie not 14 or 24 or 40 etc) you should use

SELECT * FROM foo WHERE col LIKE '%, 4,%'

or

SELECT * FROM foo WHERE col LIKE '%,4,%'

if there are no spaces between the commas and numbers

Answer 3

You database design is wrong, that's why you have problems querying the data. You should have the values in a separate table, so that teach value is in it's own field. Then it would be easy to find the records:

select t.testId
from test t
inner join listing l on l.testId = t.testId
where l.id = 4

Now you have to use some ugly string comparison to find the records:

select testId
from test
where ','+listing+',' like '%,4,%'

Answer 4

The common approach is to parse the list into a table variable or table-valued function, then either join against the table, or use an EXISTS sub-query.

There are lots of examples on how to do this:

http://www.bing.com/search?setmkt=en-US&q=SQL+parse+list+into+table

Answer 5

You can try

SELECT *
FROM YourTable 
WHERE REPLACE(Col, ' ', '') LIKE '4,%' --Starts with
OR REPLACE(Col, ' ', '') LIKE '%,4' --Ends with
OR REPLACE(Col, ' ', '') LIKE '%,4,%' --Contains
OR REPLACE(Col, ' ', '') = '4' --Equals

Just as a matter of interest, have a look at this

DECLARE @delimiter NVARCHAR(5),
     @Val INT

SELECT @Val = 40

SELECT  @delimiter = ','

DECLARE @YourTable TABLE(
     ID INT,
     Vals VARCHAR(50)
)

INSERT INTO @YourTable (ID,Vals) SELECT 1, '1,2,3,4,5,6,7,8'

DECLARE @TempTable TABLE(
     ID INT,
     Vals XML
)

INSERT INTO @TempTable 
SELECT  ID,
     CAST('<d>' + REPLACE(Vals, @delimiter, '</d><d>') + '</d>' AS XML)
FROM    @YourTable 

SELECT  *
FROM    @TempTable tt
WHERE   EXISTS(
      SELECT  T.split.value('.', 'nvarchar(max)') AS data
      FROM    tt.Vals.nodes('/d') T(split)
      WHERE T.split.value('.', 'nvarchar(max)') = @Val
)

Answer 6

You could use an instring function in the where clause and in the select clause:

Oracle:

select substr(column, instr(column, '1', 1), 1)
where instr(column, '1', 1) > 0

works if you want a single value. Alternatively you can use a combination of case or decode statements to create a single column for each possible value:

select 
 decode(instr(column, '1', 1), 0, substr(column, instr(column, '1', 1), 1), null) c1,
 decode(instr(column, '2', 1), 0, substr(column, instr(column, '2', 1), 1), null) c2,
 decode(instr(column, '3', 1), 0, substr(column, instr(column, '3', 1), 1), null) c3

The beauty of this approach for such a poorly normalised set of data is you can save this as a view and then run SQL on that, so if you save the above you could use:

select c1, c2 from view where c1 is not null or c2 is not null

NB. In other dbms you might have to use different syntax, possibly the case rather decode statement