Search String from a Group... - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Search String from a Group...

Author

Topic

kka_anand
Starting Member

24 Posts

Posted - 2010-10-31 : 15:16:20

Hi All,

I have a table with two columns. First field is a Name and second field is Degrees which has multiple values seperated by comma(,).

Name Degrees
---------------------------------------------------------------------
AVINS 131,131.35,133,135.3,136.2,135.6,135.35,135.4,135.7
---------------------------------------------------------------------

My requirement is to select a record only if a particular value exists in the Degrees field.

For example, If 135.35 exists in the Degrees field then only it should display that record.

I want something like this,
SELECT XXfunction(Degrees, 135.35) FROM Tbl1 WHERE Name='AVINS'

Is there any fuction(like substring) to meet this criteria?

Pls Advice...

Thanks & Regards
Anand

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 15:35:01

select *
from tbl
where ',' + fld + ',' like '%,135.35,%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kka_anand
Starting Member

24 Posts

Posted - 2010-10-31 : 15:56:18

Thanks nigelrivett.
It works.

Even, I have tried with patindex fuction. It works.

select * from Tbl1 where patindex('%135.35%', Degrees) <> 0

Is it possible to get a index value for 135.35?

For the value 135.35 the index value is 6.

Thanks in advance
Anand

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 16:10:58

For that you need to find the position in the string.
You could split the strings up using a cte or count the preceeding commas

select fld, LEN(s1) - LEN(replace(s1,',',''))
from (select s1 = left(@s,CHARINDEX (',135.35,',fld)), fld from tbl where ',' + fld + ',' like '%,135.35,%') a

actually - realised there is a bit of an issue that the first comma is incorrect as it is before the name rather than the first item in the csv string - I'll post a correction for that too.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 16:22:58

Thought I'd better test tis one
Thik the alvin one should be 7 not 6 - unless you start from 0

declare @s table (s varchar(1000))
insert @s select 'AVINS 131,131.35,133,135.3,136.2,135.6,135.35,135.4,135.7'
insert @s select 'xxx 135.35,135.4,135.7'
insert @s select 'yyyyyy 135.4,135.7'

select s, s1 = STUFF(s,charindex(' ',s),1,',') from @s

select s, LEN(s2) - LEN(replace(s2,',',''))
from
(
select s, s2 = left(s1,CHARINDEX (',135.35,',s1))
from (select s, s1 = STUFF(s,charindex(' ',s),1,',')+',' from @s) a
where s1 like '%,135.35,%'
) b

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 16:32:17

Here's the one using a cte

declare @s table (s varchar(1000))
insert @s select 'AVINS 131,131.35,133,135.3,136.2,135.6,135.35,135.4,135.7'
insert @s select 'xxx 135.35,135.4,135.7'
insert @s select 'yyyyyy 135.4,135.7'

;with cte as
(
select *,seq=0,loc1 = 0, loc2 = charindex(',',s1) from
(select s, s1 = STUFF(s,charindex(' ',s),1,',')+',' from @s) a
union all
select s, s1, seq=seq+1, loc1 = loc2+1, loc = CHARINDEX(',',s1,loc2+1) from cte where CHARINDEX(',',s1,loc2+1) <> 0
)
select s, seq
from cte
where seq <> 0
and SUBSTRING(s1,loc1,loc2-loc1) = '135.35'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kka_anand
Starting Member

24 Posts

Posted - 2010-10-31 : 16:35:55

Thanks nigelrivett for your support and help.

kka_anand
Starting Member

24 Posts

Posted - 2010-11-06 : 14:02:39

Hi Nigelrivett,

I have fine tuned your query as per my requirement. Now I have another requirement in the same query. In the below query we have hardcoded the value 135.35. My requirement is the value 135.35 should come from a master table (Tbl2 table). We have to put a join between two tables. Can you help me out on this.

select Name, Degrees, LEN(s2) - LEN(replace(s2,',','')) from
(
select Name, Degrees, s2 = left(s1,CHARINDEX (',135.35,',s1))
from (select Name, Degrees, s1 = ','+Degrees+',' from Tbl1) a
where s1 like '%,135.35,%'
) b

Tbl1
####

Name Degrees
---------------------------------------------------------------------
AVINS 131,131.35,133,135.3,136.2,135.6,135.35,135.4,135.7
---------------------------------------------------------------------

Tbl2 -- (Master Table)
####

Name Degrees
------------------
AVINS 135.35
------------------

Thanks in Advance...

Regards
Anand

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources