Clash Royale CLAN TAG#URR8PPP

Identifying values that have a space

We have some data in one of our database table, the structure of which is something like this.

ID NAME
1002 Total Cost
1003 Market Price
1004 Total Cost
1005 Total Cost
1006 Sales Price
1007 Gross Price
1008 Gross Price


In our UI, we have a page where we see the distinct Names from the above table. The problem is that for 'Total Cost', we see two records - one by the name 'Total Cost' and another by the name 'Total Cost ' (i.e with one extra space at the end). I'm unable to identify where the extra space is.

I see 3 records always When I execute the below query, with no matter how many extra trailing spaces.

SELECT * FROM AB_MASTER_DATA WHERE NAME LIKE '%Total Cost %'


How can I identify the bad data?

We are using MS SQL 2014.

5 Answers
5

So the string Total Cost won't be affected by normal spaces

Total Cost

However, there are "other spaces": TAB, hard space, CR, LF etc

And we've not even using nvarchar yet

Example with UNIQUE constraint on the Name columns

DECLARE @UniqueTest table (
ID int NOT NULL IDENTITY (1,1) PRIMARY KEY,
UniqueName varchar(100) NOT NULL UNIQUE
)

--OK so far
INSERT @UniqueTest VALUES ('Market Price')
INSERT @UniqueTest VALUES ('Total Cost')

--gives error
--INSERT @UniqueTest VALUES ('Total Cost ')

--tab
INSERT @UniqueTest VALUES ('Total' + CHAR(9) + 'Cost')
--hard space
INSERT @UniqueTest VALUES ('Total' + CHAR(160) + 'Cost')
-- TRAILING CR
INSERT @UniqueTest VALUES ('Total Cost' + CHAR(13))

SELECT * FROM @UniqueTest


To find rows that aren't simple alphanumeric or spaces

SELECT * FROM @UniqueTest WHERE UniqueName LIKE '%[^A-Z0-9 ]%'


The problem is in Your UI, where you are adding data.
You should TRIM your data if you want to be secure about trailing and ending spaces. But also you can have issues with Upper/Lowercase strings, so you have to manage this also.
Also you can use

SELECT DISTINCT(*) FROM AB_MASTER_DATA WHERE TRIM(NAME) LIKE 'Total Cost'


To select only one from all that satisfy the condition:

You can use LTRIM and RTRIM to UPDATE your data:

LTRIM

RTRIM

UPDATE

UPDATE AB_MASTER_DATA SET NAME = LTRIM(RTRIM(NAME))


Note: You need to make sure your application doesn't INSERT with leading or trailing spaces.

INSERT

In case you only want to SELECT the trimmed data, you can use the following:

SELECT

SELECT LTRIM(RTRIM(NAME)) FROM AB_MASTER_DATA


Since SQL server 2017 you can use TRIM instead of LTRIM and RTRIM.

TRIM

LTRIM

RTRIM

Trailing spaces, as you've found out, are ignored in string comparisons in SQL. This is part of the SQL standard. If you want to find trailing spaces you can do something like:

WHERE CONCAT(NAME, '<') LIKE '%Total Cost <'


The point being to concatenate some character after the trailing spaces, so they are no longer ignored.

You can identify the records using:

where name like 'Total Cost_%'


'_' is a wildcard that matches any character, but requires a character to be there.

'_'

You can identify the character (if it is ASCII) by doing something like:

select ascii(substring(name, 11, 1))


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.