-3

I have a REALLY odd one here.

I have 2 rows in a table where the data in the same column on running a SELECT query is identical bar a trailing space (or perhaps another whitespace char) in one of them. Column is declared varchar(max). I want to treat both values as the same (to do a GROUP BY, as it happens).

After RTRIM did not work, I looked at the varbinary of both ... and they are identical. But they are not, I can see a space and GROUP BY treats them as seperate values (that is my real problem).

I already tried replacing CHAR(13) - it is not that.

Any clues?

CODE:

declare @vals table
(
    id int identity primary key,
    textval varchar(max)
)

insert into @vals (textval) values ('07.09.17 Liefertermin weiterhin nicht bekannt (LZ Elmshorn)')
insert into @vals (textval) values ('07.09.17 Liefertermin weiterhin nicht bekannt (LZ Elmshorn)
')

select id, textval + '*' from  @vals
select id, RTRIM(LTRIM(REPLACE(textval,CHAR(13),''))) + '*' from  @vals

select textval from @vals group by textval
kpollock
  • 3,899
  • 9
  • 42
  • 61
  • 2
    Most likely, because there's nothing wrong, nothing to trim. The *comparison* may be wrong. The values may be actually different. Instad of `varchar` or `varbinary` one of the values is fixed-length. Perhaps that character at the end is *not* a space but another character. Post a *reproducible* example – Panagiotis Kanavos Sep 08 '17 at 10:28
  • For example, storing 6 characters in a `char(8)` instead of `varchar(8)` will result in padding, because `char(8)` has a *fixed* length. You may not notice because SQL (the standard's) rules require that [strings get padded to the same length](https://support.microsoft.com/en-us/help/316626/inf-how-sql-server-compares-strings-with-trailing-spaces) before matching – Panagiotis Kanavos Sep 08 '17 at 10:32
  • it is the in the SAME column. – kpollock Sep 08 '17 at 10:34
  • Ok, then you can tell us what's wrong. *We* can't, because we have no info at all - no table schema, no data, no query. How can *anyone* help, unless he/she is standing on the desk next to you? – Panagiotis Kanavos Sep 08 '17 at 10:35
  • short of givng you access to my database, I can't see how to give any more info. Which I can't do. It IS happening, definitely. – kpollock Sep 08 '17 at 10:37
  • If you want an answer, post a minimal, reproducible example. A *small*, if not the smallest, script that *can* be executed without guesswork and shows the problem when run. For SQL problems, that is a table creation statement, `INSERT` statements with test data, a query that shows the problem and the expected vs actual result – Panagiotis Kanavos Sep 08 '17 at 10:38
  • If it was happening defiinitely, thousands of DBAs and developers would have found out 20 years ago. At least. What is happening though, is people getting confused, storing data using the wrong codepage, missing characters that look the same yet aren't, like space vs one of the many non-braking spaces. – Panagiotis Kanavos Sep 08 '17 at 10:40
  • You haven't even explained *how* you know there is a problem. What *query* did you run, what did you expect and what did you get back? – Panagiotis Kanavos Sep 08 '17 at 10:41
  • I appreciate that would be useful.. Especially as a 10+ year veteran here (I really can't recall) and 23 years as a software dev. But how do you suggest I get the exact column values that look EXACTLY like the same value but one with a trailing space into the INSERT in such an example. I doubt cut n paste will do it... – kpollock Sep 08 '17 at 10:41
  • Use `DATALEN()` to see whether they are the same length. Escape single quotes and write `SELECT '''' + someColumn + '''' as output`. There are a *lot* of similar SO questions. Anyway, that doesn't prevent you from posting the schema and query – Panagiotis Kanavos Sep 08 '17 at 10:48
  • Click on the result cell in SSMS and hit `Ctrl + C` is another one. Anyway, with 20 years experience you definitely heard `Think horses, not zebras` before – Panagiotis Kanavos Sep 08 '17 at 10:49
  • I was not sure if that would paste accurately into other places like SO box, but if you reckon it will, I can try!! – kpollock Sep 08 '17 at 10:54
  • I see a newline in the second INSERT. Was that a copy/paste error, or did the second string contain a newline? – Panagiotis Kanavos Sep 08 '17 at 11:00
  • no error. that is exact. As I said, removign the newline does not (entirelöy solve the issue). – kpollock Sep 08 '17 at 11:02
  • 1
    Solve *what* issue ? BTW did you check what the character actually is? CHR(13), 10 both? – Panagiotis Kanavos Sep 08 '17 at 11:02
  • how to make GROUP BY see the two strings as identical. As is stated twice in the question. – kpollock Sep 08 '17 at 11:03
  • anyway, clearly I just need to go through the list of all whitespace characters - looks lioke there is more than just the new line – kpollock Sep 08 '17 at 11:04
  • No, you didn't. That's the first time the newline appears. Besides, CHR 13, 10 or both? What is the **length** of both strings? Did you check it? DId you use `DATALEN` in both cases? You *know* you have extra characters, don't *assume* what they are – Panagiotis Kanavos Sep 08 '17 at 11:05
  • When you do, you'll see that `datalength(textval)` returns 59 and 61. You replaced only 1 character when you should have replaced 2 – Panagiotis Kanavos Sep 08 '17 at 11:06
  • This is a duplicate of [how i can remove all NewLine from a variable in SQL Server?](https://stackoverflow.com/questions/18073190/how-i-can-remove-all-newline-from-a-variable-in-sql-server) – Panagiotis Kanavos Sep 08 '17 at 11:09

1 Answers1

0

Turns out to have been carriage return + linefeed CHAR(13) CHAR(10)

Thanks to Panagiotis Kanavos for bending my Friday brain (eventually) down the right path.

I found the following useful https://www.codeproject.com/Tips/330787/LTRIM-RTRIM-doesn-t-always-work

kpollock
  • 3,899
  • 9
  • 42
  • 61