[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • Last Modified:

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
0
RIAS
Asked:
RIAS
  • 5
  • 3
1 Solution
 
Trideep PatelProject LeadCommented:
What is the value in your database table is it 'abc' or ' abc' with blank?
0
 
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
 
RIASAuthor Commented:
Great Solution as always! Cheers!
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now