Simple SQL solution required

[BadgerByte]

Hey all,

My problem is quite straightforward I have a field in the database into which people are placing Car registration numbers. They are then relying on this car reg to find clients and to know whether or not this is a new client.

However, some people are placing spaces in the Registration number ie it could turn out like:-

AB74 BYM
AB74BYM
AB 74BYM

The database is already in heavy use so I can't just remove the spaces when they create a new client (as I should have!!).

Is there any use of the LIKE statement or any other I can use to make sure the search includes all possible combinations of spaces?

Many thanks,

Andy

 

[kulrom]

There is a TRIM() SQL function and it should be just fine solution if i remember well ... have a look of this function ... try with google (sorry for my ignorance but i have to go now) ... notice that blah blah WHERE LIKE '%AB74BYM%' is matching all these from above if they are trimmed.

if you don't find anything useful i'll try to help you more when i come back ...

Regards

Edit: actually trim will remove only first and last spaces but not the ones in the middle ... so ignore this advice

 

[kulrom]

try with SELECT SUBSTRING function

 

[kulrom]

Ok i stayed a bit longer ... nevermind ... i think i have an adequate SELECT statement for you

SELECT Replace('" & the String & "', ' ', '') ' will return theString

Just replace the String with appropriate value and that's it ...

HTH
Regards

 

[BadgerByte]

Thank you kulrom once again.. I just came up with the exact same answer after a quick search on Trim. Works a treat. Thank you once again for your help.

 

[TechGnome]

Yeah, Trim won't work as it'll only trim from the front & back... but the replace should do just nicely.

-tg