Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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