Search code examples
sqlselectsql-server-ce

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.

Solution

  • 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"))