Wildcard in declared variable doesn't work

Question

Post reply

Wildcard in declared variable doesn't work

SpeedSkaterFan

SSCrazy

Points: 2716
More actions
September 29, 2015 at 1:10 pm

#308395

Hi all.
As part of a bigger project I am trying to select all rows that comply to certain values (Johnson, Parra and Groves).
The code I came up with works fine, except for the last part in the where clause where I try to built in a wildcard so that all records that CONTAIN either Johnson, Parra or Groves are selected.
So far I came up with:
USE myDatabase
GO
DECLARE @LastName table
(
Value NVARCHAR(100)
)
INSERT INTO @LastName Values ('Johnson')
INSERT INTO @LastName Values ('Parra')
INSERT INTO @LastName Values ('Groves')
Select * FROM tbPersons WHERE prsLastName IN (SELECT Value FROM @LastName WHERE Value LIKE '%' + Value + '%')
If I leave out the "WHERE Value LIKE '%' + Value + '%')" part, it all works fine, after adding it, it doesn't anymore.
I have tried many variations with '%' and with Value etc, none works
Is it a syntax error or is there more wrong?
Hein

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What are you trying to accomplish with the condition WHERE Value LIKE '%' + Value + '%'?

Unless NULL values are involved, that should always evaluate to true.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 2

Hi Louis

Good to hear from you again!

What I am trying to do is, in a database for speedskating, get all records that have e.g. the word 'World', or 'Nederlands' in it.

The names I gave are just a variation on this.

The names used in the column are not under my supervision, they are filled in by others.

Names can be eg.: '53rd World championships sprint', or '110th Nederlandse kampioenschappen mannen' etc. Very wrong indeed, but a fact for me.

The part you referring to is wrong, I know

What I am trying to do is to get a where clause in the where clause: like '%World%' or like '%Nederlandse%' etc. Values that are stored in the @lastname table

The parts 'world', 'Nederlandse' can change, therefore the insertion in the table varies

I hope this clears things for you?

Hein

Bill Talada SSChampion Points: 11954 More actions · Answer 3

It works fine for me either way, with or without the WHERE clause.

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 4

Hi Bill

Did you do the select on a different table (named tbPersons) with values in field prsLastName ?

If in your 1st tryout the names in the @Lastname table match the names in tbPersons then it works (with me also)

However: if you change the names in @Lastname, eg by removing the 1st letter, I am sure it doesn't work anymore

Hein

Bill Talada SSChampion Points: 11954 More actions · Answer 5

Eureka! I think I figured out what you're trying to do.

DECLARE @Names TABLE (LastName varchar(50) NOT NULL);

INSERT into @Names values ('Robertson'), ('Johnson'), ('Smith'), ('Smithwyck'), ('Miller');

DECLARE @searches TABLE (token VARCHAR(50) NOT NULL);

INSERT INTO @searches Values ('%son'), ('smith%');

SELECT * FROM @names;

SELECT * FROM @searches;

Select *

FROM @Names n

CROSS APPLY (select * FROM @searches s where n.LastName like s.token) AS s

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

For an alternative syntax, you can also just join on the LIKE (in Bill's code, added while I was typing this you wouldn't need to add the wildcards, since they're already in the search string).

DECLARE @ContainsNames table

(

ContainsValue NVARCHAR(100)

)

INSERT INTO @ContainsNames Values ('JohnsonPrefix')

INSERT INTO @ContainsNames Values ('SurroundParraSurround')

INSERT INTO @ContainsNames Values ('SuffixGroves')

INSERT INTO @ContainsNames Values ('NoMatchGoves')

DECLARE @LastName table

(

Value NVARCHAR(100)

)

INSERT INTO @LastName Values ('Johnson')

INSERT INTO @LastName Values ('Parra')

INSERT INTO @LastName Values ('Groves')

SELECT CN.ContainsValue

FROM @LastName LN

INNER JOIN @ContainsNames CN

ON CN.ContainsValue LIKE '%'+LN.Value+'%'

Cheers!

SpeedSkaterFan SSCrazy Points: 2716 More actions · Answer 7

Hi Ten Centuries

Thanks for your reply,

As far as I can see your code works just the other way around from what I am looking for.

In my example I have a table (tbPersons).

From this table I would like all records where prsLastname contains the words inserted into the @LastName

So: @lastname value John would return all records from tbPersons like, John, Johns, Johnson, Johnsson etc

Bill,

I think this is it 🙂

I am not yet familiair with 'cross apply' but in the meantime I have searched for some information and this looks like a valuable addition that I can use in multiple cases.

Thanks for that.

I thought it would be easier (and use some % as wildcards) but that's my mistake.

i will dig into this and report back to you

Thanks for now

Hein

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

Ah, my bad. That was just the fault of my removing the the wrong column from the SELECT list.

I had originally included both columns in the SELECT, and just removed the wrong one. Same query, just showing a different column 🙂

I've edited the original post.

Cheers!