Search for strings with a white space in sql

Hello,

I have a stored procedure to search a string but, it fails when it has white spaces at the beginning of it.
Example: 'abc'  searches perfectly but, ' abc' fails. Cannot use like in the query.
The query I have is
  SET @SQL = @SQL + '  AND Auth = ''' +  CAST(@Auth AS NVARCHAR(50))+ ''''	 

Open in new window


Regards
RIASAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Use LTRIM to remove the spaces from the beginning of the string. You can also use RTRIM if you want to remove the spaces at the end of the string. So, the following will remove spaces from begin and end of the string:
SET @SQL = @SQL + '  AND Auth = ''' +  LTRIM(RTRIM(CAST(@Auth AS NVARCHAR(50)))) + ''''	 

Open in new window

0
 
Trideep PatelProject LeadCommented:
What is the value in your database table is it 'abc' or ' abc' with blank?
0
 
RIASAuthor Commented:
Great Solution as always! Cheers!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RIASAuthor Commented:
Vitor,
Sorry for the confusion.
The data in the table is with space  example ' abc'
So need to search that.  Your solution trims the string for search but the data is with space

Cheers
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you mean when @Auth = ' abc' (with leading space) your original query doesn't work?
0
 
RIASAuthor Commented:
The data in the table is ' abc'.
my original query was
SET @SQL = @SQL + '  AND Auth = ''' +  CAST(@Auth AS NVARCHAR(50))+ ''''	 

Open in new window


was not able to give result.
It is just one of them as cannot change data in table.
Can I  have a query which searches even the ones with leading spaces
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. It's the data in the table that has the space and not the variable. So use my above solution but on the column name. Mind that this can reduce the query performance if Auth is indexed:
SET @SQL = @SQL + '  AND LTRIM(RTRIM(Auth)) = ''' +  CAST(@Auth AS NVARCHAR(50))+ ''''	 

Open in new window

1
 
RIASAuthor Commented:
Thanks will try and be right back
0
 
RIASAuthor Commented:
Worked like charm! Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.