Tricky SELECT - 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

General SQL Server Forums

New to SQL Server Programming

Tricky SELECT

Author

Topic

Access
Starting Member

44 Posts

Posted - 2007-07-18 : 09:38:10

I have a LastName field which holds this data
LastName
Johnson|Vasques
Adams|Fox|Johnson
Vasques|Smith

Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.

The @LastName can be something like this: “Fox|Smith”.
I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).

In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith .

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-18 : 09:41:47

why are you storing multiple value in the same column ?

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

Access
Starting Member

44 Posts

Posted - 2007-07-18 : 09:52:15

quote:
Originally posted by khtan

why are you storing multiple value in the same column ?

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

I new it's coming...but long story short, it is not a physical table, it is a TABLE variable within a stored procedure where I temporary store the data

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-18 : 10:05:25

in that case why not just store one value in the LastName column ?

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

Access
Starting Member

44 Posts

Posted - 2007-07-18 : 10:09:56

quote:
Originally posted by khtan

in that case why not just store one value in the LastName column ?

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

There is logic behind it, but I'm sorry, I wish not to go into those details in this topic.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-18 : 10:12:22

make use of the split / parse string function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-18 : 10:26:33

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Access
Starting Member

44 Posts

Posted - 2007-07-18 : 10:59:51

quote:
Originally posted by khtan

make use of the split / parse string function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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

Thanks for your reply.

I have a splitter function which is basically splits the @LastName variable(FOX|SMITH), but problem i'm facing is how do i say "Where LastName LIKE"

In the below example the IN operator is not going to work , since the value in LastName field can be "Adams|Fox|Johnson" or "Johnson|Vasques" or "Vasques|Smith", so i need to use LIKE operator somehow.

Select * From MyTable Where LastName IN (SELECT LastName FROM dbo.fx_cl_Splitter(@LastName, '|'))

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-18 : 12:35:22

USE THIS ONE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

It returns a table of each separate name and you can use

Select * From MyTable Where LastName IN (SELECT LastName FROM dbo.split(@LastName, '|'))

Jim

Access
Starting Member

44 Posts

Posted - 2007-07-18 : 12:45:39

quote:
Originally posted by jimf

USE THIS ONE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

It returns a table of each separate name and you can use

Select * From MyTable Where LastName IN (SELECT LastName FROM dbo.split(@LastName, '|'))

Jim

My split function also return a Table of each separet name.
I do not know if you sow my reply just above yours, but here it is once again:

The IN operator is not going to work , since the value in LastName field can be "Adams|Fox|Johnson" or "Johnson|Vasques" or "Vasques|Smith", so i need to use LIKE operator somehow.

Select * From MyTable Where LastName LIKE (SELECT LastName FROM dbo.fx_cl_Splitter(@LastName, '|')) , but LIKE not good here either since fx_cl_Splitter function returns more the 1 value.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-19 : 05:04:01

Try

Select columns from table t inner join (SELECT LastName FROM dbo.fx_cl_Splitter(@LastName, '|')) F
on t.lastname like '%'+f.lastname+'%'

Madhivanan

Failing to plan is Planning to fail

Access
Starting Member

44 Posts

Posted - 2007-07-19 : 12:20:19

quote:
Originally posted by madhivanan

Try

Select columns from table t inner join (SELECT LastName FROM dbo.fx_cl_Splitter(@LastName, '|')) F
on t.lastname like '%'+f.lastname+'%'

Madhivanan

Failing to plan is Planning to fail

Thank you Madhivanan, that did it. I actually start loosing a hope that somebody will answer that

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 12:35:43

quote:
Originally posted by Access

quote:
Originally posted by madhivanan

Try

Select columns from table t inner join (SELECT LastName FROM dbo.fx_cl_Splitter(@LastName, '|')) F
on t.lastname like '%'+f.lastname+'%'

Madhivanan

Failing to plan is Planning to fail

Thank you Madhivanan, that did it. I actually start loosing a hope that somebody will answer that

I had already given a similar answer over here.. Perhaps you forgot to check this: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=22844

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

Access
Starting Member

44 Posts

Posted - 2007-07-19 : 12:39:13

quote:
Originally posted by dinakar

quote:
Originally posted by Access

quote:
Originally posted by madhivanan

Try

Select columns from table t inner join (SELECT LastName FROM dbo.fx_cl_Splitter(@LastName, '|')) F
on t.lastname like '%'+f.lastname+'%'

Madhivanan

Failing to plan is Planning to fail

Thank you Madhivanan, that did it. I actually start loosing a hope that somebody will answer that

I had already given a similar answer over here.. Perhaps you forgot to check this: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=22844

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

Yes you are 100% right, i forgot to check it in there

, and YES you gave a right answer, Thanks for that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources