SELECT LIKE statment

Question

SELECT LIKE statment

Magy

SSCarpal Tunnel

Points: 4217
More actions
September 9, 2005 at 12:08 pm

#87630

I'm having issues using the LIKE command. I have a column in a database that contains serial numbers from different vendors. Some of the serial numbers have spaces, some don't. For example:
'444 54 32222 33'
'3239483721dfvv'
'gf4837 1 24 54 78'
I have a form on a website that searches for these serial numbers. However, the LIKE statement doesn't return anything if the user enters in the claim without spaces. For example, if the user is searching for:
'444 54 32222 33'
and they type:
'444543222233'
No results will get returned. Is there a way around this ?
Thanks!

Viewing 15 posts - 1 through 15 (of 48 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Create an indexed calculated column that strips out the spaces. Then do the search on the column (making sure that the search parameter doesn't have spaces too).

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Use the REPLACE function in both your input and the field searched to remove spaces for the match. (Don't worry, someone will probably give you a better solution that may not ruin your index if one exists on this field... ).

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Wow - no sooner said, than done...

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Well sooner done than said... You were still a little late on this one .

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 5

Forgive my ignornace, but how does one create an indexed calculated column that strips out the spaces?

Thanks!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

ALTER TABLE dbo.Temps ADD

demo AS replace(EPNOM, ' ', '')

GO

CREATE NONCLUSTERED INDEX IX_Temps_Demo ON dbo.Temps (demo)

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 7

Ok, I understand creating an index...but what is dbo.Temps? Is that a table that I first need to create? And I assume the demo table is the table I'm using to actually do the query on.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

dbo.Temps is the table name

demo is the name of the new column.

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 9

Oh I see....

What does EPNOM mean? I googled it and couldn't find much of anything.

Farrell Keough SSCoach Points: 17414 More actions · Answer 10

You would put the real name of your table and real name of the column you want to create in their place...

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

EPNOM is the name of the other column you need to replace the spaces from.

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 12

Oh I see...

I ran the query and got this error:

Warning! The maximum key length is 900 bytes. The index 'IX_serialnumbers_serialsnospaces' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Doh...

try it like this :

LEFT(REPLACE(Colname, ' ', ''), (length of the original column)

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 14

If I'm using the replace function when creating this new column, how come I can't just use the replace function when doing the original query? That way I wouldn't have to worry about a whole new column to update.

Thanks!