CODEWITHSUNDEEP
sqlselectsql-server-ce
Ghasem
Ghasem

Reputation: 15573

SQL query to ignore space

Is there anyway to ignore spaces in a SQL query only from right and left of a string so that I can find ' alex ' (not ' i am alex jolig ') by searching for 'alex'?

I've read this question and its answer, but the solution removes all the spaces in the string.

SELECT * FROM mytable 
    WHERE REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')

By the way, I'm using Sql Server Compact Edition.

Thank you.

Upvotes: 3

Views: 4269

Answers (2)

NarayanaReddy
NarayanaReddy

Reputation: 301

You can use RTRIM() to remove spaces from the right of string and LTRIM() to remove spaces from the left of string.

Hence left and right spaces removed as follows:

SELECT * FROM tableName
WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("bod alias baby"))

Upvotes: 0

KAD
KAD

Reputation: 11112

You can use RTRIM() to remove spaces from the right and LTRIM() to remove spaces from the left hence left and right spaces removed as follows:

SELECT * FROM mytable 
    WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("John Bob Jones"))

Upvotes: 4

Related Questions