Strip punctuation from MSSQL value

growse

growse

growse

Soldato
Joined
18 Oct 2002
Posts
7,139
Location
Ironing
I'm trying to strip all punctuation out of a value in a MSSQl database so that I can compare it to a string that won't have any punctuation. For various reasons, I'm replacing the spaces as well with hyphens. Therefore, a given string of:

hello-there-everyone

should match a database value of

Hello There! Everyone!

Currently I've got
Code: 
select * from table where REPLACE(field,' ','-') = @input
which takes care of the spaces/hyphens issue. Is there any way to strip out the punctuation without nesting lots of replace statements?
 
growse said:
I'm trying to strip all punctuation out of a value in a MSSQl database so that I can compare it to a string that won't have any punctuation. For various reasons, I'm replacing the spaces as well with hyphens. Therefore, a given string of:

hello-there-everyone

should match a database value of

Hello There! Everyone!

Currently I've got
Code: 
select * from table where REPLACE(field,' ','-') = @input
which takes care of the spaces/hyphens issue. Is there any way to strip out the punctuation without nesting lots of replace statements?
Click to expand...

Mneh..select where the field is LIKE "hello%there%everyone" (can't remember if that's the right wildcard character)? If that's selecting too much, as in "hello there mum I've had a great time here with everyone" you could weed out false hits in code maybe?
This is probably too hacky of course...

Or...split the input phrase on the hyphen word-boundaries and "select blah from table where field LIKE "%word1%" AND field LIKE "%word2%" AND etc...."

Depending on the size of the data set of course, the first way may be sufficient...just use PHP regex to replace combinations of spaces/punctuation as a single hypen for each field returned and only act on matches...

What is the purpose of the field containing the value "Hello there! Everyone"? Could you create another column that acts as a sort of index on this, i.e. store the "hello-there-everyone" value in the row from the get-go?
 
From looking at a couple of articles I think you can use regular expressions directly in a MySQL query, in MySQL 5 anyway, using the REGEXP keyword. Haven't tried it myself (apologies if I've got it wrong) but could be worth a look.

If this is the case you could use a regexp to match the words in your search, ignoring any non-word characters between them.
 
You must log in or register to reply here.
Back
Top Bottom