want solution of compare between two string

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

General SQL Server Forums

New to SQL Server Programming

want solution of compare between two string

Author

Topic

ankitshah2003
Starting Member

5 Posts

Posted - 2008-08-27 : 06:10:21

hi all i stuck up with one problem
i want to fire one query where i want to check the string like

@lc='1,9,150'

with the one field f2 of table which has number of rows contain data like

field value
=============
f1 f2
== ==
1 1,337,
2 337,150,
3 150,9,
4 111,23
5 9,23

as result i want to get the set like

1 1,337,
2 337,150,

if anybody knows plz help me

thanks

Ankit shah

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 06:17:44

[code]DECLARE @lc varchar(10)

DECLARE @sample TABLE
(
f1 int,
f2 varchar(10)
)

INSERT INTO @sample
SELECT 1, '1,337,' UNION ALL
SELECT 2, '337,150,' UNION ALL
SELECT 3, '150,9,' UNION ALL
SELECT 4, '111,23' UNION ALL
SELECT 5, '9,23'

SELECT @lc ='1,9,150'

SELECT DISTINCT s.f1, s.f2
FROM @sample s
CROSS apply CSVTable(s.f2) f2
INNER JOIN CSVTable(@lc) lc ON f2.stringval = lc.stringval
/*
f1 f2
----------- ----------
1 1,337,
2 337,150,
3 150,9,
5 9,23

(4 row(s) affected)
*/[/code]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-27 : 06:29:29

Why are you excluding

3 150,9,
5 9,23

???

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 06:46:18

quote:
Originally posted by Peso

Why are you excluding

3 150,9,
5 9,23

???

E 12°55'05.25"
N 56°04'39.16"

seems like OP gave only part of result

ankitshah2003
Starting Member

5 Posts

Posted - 2008-08-27 : 06:49:11

hi khtan,
u understand perfectlly but that csvtable is not working in sql server2005 and another thing that i want to check the @lc variable value to the feild of table which has numbers of rows and columns so this solution can works for it?
thanks

Ankit shah

ankitshah2003
Starting Member

5 Posts

Posted - 2008-08-27 : 06:54:50

hi Peso
i also want that two rows into my result set only
4 '111,23'
is not in the result

if u have solution then tell me.

thanks

Ankit shah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 07:51:40

Try this then
:-

declare @Test table
(
f1 int,
f2 varchar(100)
)
declare @amount  varchar(100)
set @amount='1,9,23'
insert into @test
select 1, '1,337,' union all
select 2, '337,150,' union all
select 3, '150,9,' union all
select 4, '111,23' union all
select 5, '9,23'

select DISTINCT t.* 
from @test t
cross apply dbo.ParseValues(t.f2,',') b
cross apply dbo.ParseValues(@amount,',')c
where b.Val=c.Val 

output
---------------------------
f1          f2
----------- -------------------
1           1,337,
3           150,9,
4           111,23
5           9,23

the function parsevalues can be given below
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485&SearchTerms=parsevalues

ankitshah2003
Starting Member

5 Posts

Posted - 2008-08-27 : 08:04:17

hi Vishakh16 this seems to not works for that any other solution thanks

Ankit shah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 08:06:06

quote:
Originally posted by ankitshah2003

hi Vishakh16 this seems to not works for that any other solution thanks

Ankit shah

what's the error obtained? give some sample data of where this does not work.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 08:11:08

quote:
Originally posted by ankitshah2003

hi khtan,
u understand perfectlly but that csvtable is not working in sql server2005 and another thing that i want to check the @lc variable value to the feild of table which has numbers of rows and columns so this solution can works for it?
thanks

Ankit shah

What do you mean CSVTable not working in SQL Server 2005 ? Do you get any error ? The query i posted is running on SQL 2005

Can you also explain what do you mean by "i want to check the @lc variable value to the feild of table which has numbers of rows and columns" ?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 08:33:40

Read about Normalization

Madhivanan

Failing to plan is Planning to fail

ankitshah2003
Starting Member

5 Posts

Posted - 2008-08-27 : 09:36:05

hi kh
sorry i don't know abt CSVTable function
but after that i tried that link and get the CSVTable function and now it is working
thanks a lot

Ankit shah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources