CODEWITHSUNDEEP
sqlsearchformat
New Guy
New Guy

Reputation: 596

SQL Search Query, Search for String Omit Spaces and Convert to Lower Case

My wording may not be correct so the solution could be on this site. In which case, pointing me to the solution would be great. Anyway, I am doing a search and need to do compares against the string and value that is in a column. It has to go beyond LIKE. For instance, I want to take the column put it in lower case and take out all spaces before comparing it to the string (which is too put in lower case and all spaces are gone). I want to do this without modifying the contents of the column. So just for the compare. Then if the compare evaluates to true take the original contents out of the column (not in the lower case and no spaces form). This may be to specific. Is it possible to do this, if so, any code sample would help. So I have something like this now:

SELECT *
FROM [MY_TABLE]
WHERE (
       [MY_COLUMN] LIKE My_Search_String
      )

so the My_Search_String is already formatted. I just need to format the [MY_COLUMN] without permanently modifying its contents. Any help is appreciated. Thank you!

Upvotes: 0

Views: 1988

Answers (2)

Pratik Patel
Pratik Patel

Reputation: 78

I think below query will solve your problem.!

IF EXISTS(select * from [MY_TABLE] where LOWER(REPLACE([MY_COLUMN],' ','')) like 'My_Search_String')

     INSERT INTO Another_Table
     select * from [MY_TABLE] where LOWER(REPLACE([MY_COLUMN],' ','')) like  'My_Search_String'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Upper and lower case only make a difference if your default collations or column collations are case sensitive. In any case, you seem to want:

SELECT *
FROM [MY_TABLE]
WHERE lower(replace([MY_COLUMN], ' ', '')) LIKE lower(replace(My_Search_String, ' ', ''))

Upvotes: 2

Related Questions