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.
2 Answers
You would use replace:
where str = replace(@str, ' ', '')
@str is the user input and str is the column inthe table.
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
1 Comment
Russj
How to trim spaces in the middle?