CODEWITHSUNDEEP
sql
Santanu
Santanu

Reputation: 8064

sql query complex

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.

Upvotes: 0

Views: 189

Answers (6)

aaron
aaron

Reputation: 748

How about this?

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

Upvotes: -3

Adriaan Stander
Adriaan Stander

Reputation: 166616

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
)

Upvotes: 3

Chris
Chris

Reputation: 2477

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

Upvotes: 0

Brannon
Brannon

Reputation: 26109

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

Upvotes: 0

Guffa
Guffa

Reputation: 700910

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,%'

Upvotes: 3

AndrewDFrazier
AndrewDFrazier

Reputation: 57

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

Upvotes: -1

Related Questions