Tricky query using % - 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 query using %

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-03-08 : 11:21:45

Hello there

I have a query to filter out a list that are already in our database
from a temp table. I am using the following query.

select * from test t1 where not exists(
select * from member mem, individual ind
where ind.surname = t1.[last name]
and ind.forenames = t1.[first name]
and mem.join_date is not null
and mem.leave_date is null
and mem.member_status = 33)

this query is working fine and is extracting what it should do.

some names have double barrelled names.

so i want to beable to incorrporate a % after the [first name] column.

could someone point me in the right direction please.

Kind Regards

Rob

MCTS / MCITP certified

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-08 : 11:36:03

Can you please explain by using an example?
Because I can't understand what you want to get...

No, you're never too old to Yak'n'Roll if you're too young to die.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-03-08 : 11:39:32

I have tried using the following

select * from test t1 where not exists(
select * from member mem, individual ind
where ind.surname = t1.[last name]
and ind.forenames = (select [first name] from test where [first name] like '%_%')
and mem.join_date is not null
and mem.leave_date is null
and mem.member_status = 33)

but i am getting the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-08 : 12:34:41

Try:
select * from test t1 where not exists(
select * from member mem, individual ind
where ind.surname = t1.[last name]
and ind.forenames = t1.[first name]
and ind.forenames like '%[_]%'
and mem.join_date is not null
and mem.leave_date is null
and mem.member_status = 33)

If this isn't what you want then please show us example data and wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-08 : 14:24:01

quote:
Originally posted by masterdineen
and ind.forenames = (select [first name] from test where [first name] like '%_%')

if I got your intention, replace code above with


and ind.forenames in (select [first name] from test where [first name] like '%_%')

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-03-08 : 15:44:01

is there a way to concatinate column + _%

so column + space + anything else after.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-09 : 07:30:32

quote:
Originally posted by masterdineen

is there a way to concatinate column + _%

so column + space + anything else after.

column + ' %'

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-03-09 : 08:30:27

I was a bit late in looking at your reply but i also came up with the same answer

( where ind1.forenames like t1.[first name] + '%' )

but thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources