CODEWITHSUNDEEP
sqlstringsearchmatch
Russj
Russj

Reputation: 727

How to write the query to search a string has different space in SQL Server?

For example, the stored string could be '123456789', but the user might input '123 456 789' to search, or "1234 56789". How to handle the space in different location? I use SQL Server2008 Express.

Upvotes: 0

Views: 132

Answers (2)

user3835327
user3835327

Reputation: 1204

Can try using Trim function to remove white space from a field.

select

SELECT  TRIM(fieldname)
,            LTRIM(fieldname)
,            RTRIM(fieldname)
,            LTRIM(RTRIM(fieldname))
FROM     tablename

update

UPDATE
TableName
SET
ColumnName = LTRIM(RTRIM(ColumnName))

TRIM() will remove both leading and trailing white spaces;
LTRIM() removes leading white spaces (i.e. from the beginning of a string);
RTRIM() removes trailing white space (i.e. from the end of a string);
If TRIM() is not supported, then LTRIM(RTRIM()) will achieve the same result.

SELECT LTRIM(RTRIM(YourColumn)) FROM YourTable

For more information : TRIM FUNCTION

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You would use replace:

where str = replace(@str, ' ', '')

@str is the user input and str is the column inthe table.

Upvotes: 1

Related Questions