August 3, 2018 at 12:44 pm
Hello fellows,
Today one of the team was working in a SSIS package and it had some issues and he tracked down to one row in specific..
We got some sort of code in that table like an identifier to an specific employee.
select replace(EmployeeCode,' ','') as EmployeeCode
from Employee
WHERE EmployeeCode LIKE N'232ABC2343%'
The result is the next thing..
EmployeeCode
232ABC2343
so where's the problem? whenever we use LTRIM(RTRIM(PersonCode)) it would still return the EmployeeCode but with some sort of weird character like a whitespace, its on the result below.
We even tried the Replace(EmployeeCode,' ','') in the select clause and in the where clause and still returned it with that weird character.
SELECT ltrim(rtrim(EmployeeCode)),len(EmployeeCode),*
FROM dbo.Employee
WHERE EmployeeCode = N'232ABC2343'
--result set
EmployeeCode
(0 rows affected)
if we want to trackdown that specific row we could just put a WHERE = code
but it RETURNS NOTHING! :blink:
SELECT EmployeeCode
FROM dbo.Employee
WHERE EmployeeCode = N'232ABC2343'
EmployeeCode
(0 rows affected)
The query above returns nothing also...
We tried to find through ASCII code also convert it take the weird whitespace ascii convert it back but it returns nothing.
whenever we converted the data from nvarchar to ascii the whitespace was 032 wich is space
found it here:https://www.sciencebuddies.org/science-fair-projects/references/table-of-8-bit-ascii-character-codes
We also tried.
select replace(EmployeeCode,' ','') as EmployeeCode
from Employee
WHERE replace(EmployeeCode,' ','') = N'232ABC2343'
EmployeeCode
(0 rows affected)
It'd be great if you had any kind of tips or if it has ever happened to you.
Greetings .
August 3, 2018 at 12:50 pm
It was a tab... a weird small tab.. that looks like a space...
Also really sorry for posting in the wrong forum section!!.. D:
August 3, 2018 at 1:12 pm
Alejandro Santana - Friday, August 3, 2018 12:50 PMIt was a tab... a weird small tab.. that looks like a space...Also really sorry for posting in the wrong forum section!!.. D:
It was most likely just a normal tab. In grid view, SSMS converts tabs to spaces. I would not depend on SSMS grid view for troubleshooting anything like this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2018 at 3:09 pm
drew.allen - Friday, August 3, 2018 1:12 PMAlejandro Santana - Friday, August 3, 2018 12:50 PMIt was a tab... a weird small tab.. that looks like a space...Also really sorry for posting in the wrong forum section!!.. D:
It was most likely just a normal tab. In grid view, SSMS converts tabs to spaces. I would not depend on SSMS grid view for troubleshooting anything like this.
Drew
Didn't knew! thanks alot for that information!
Kudos!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply