missing concatenated star
-
Hi all,
I have got something weird, I've never seen before.Running the query ...
select ccc_id, town, '*' + rtrim(TOWN) + '*', *
from CLI_CONTACTS
where CLC_ID = '244_'
returns following dataset
ccc_id town (No column name)
3802 Berlin *Berlin*
26880 Berlin *Berlin
40921 Berlin *Berlin*
41547 Berlin *Berlin*
65604 Berlin *Berlin*Why is the trailing star missing on the second line?
Any suggestion will be much appreciated.
Thanks - rot-717018 - Tuesday, March 28, 2017 7:32 AM
Are you sure that you don't have some kind of blank space different to the normal space? Possibilities are tabs, line feeds, hard spaces, etc.
What's the result of this query?
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT SUBSTRING( town, n, 1), ASCII(SUBSTRING( town, n, 1))
FROM CLI_CONTACTS c
JOIN cteTally t ON LEN(c.town) >= t.n
where CLC_ID = '244_'
AND ccc_id = 26880; -
Thanks Luis!
I did not know/remember about the ASCII function.
The result is
3802 Berlin 105
3802 Berlin 110
26880 Berlin 66
26880 Berlin 101
26880 Berlin 114
26880 Berlin 108
26880 Berlin 105
26880 Berlin 110
26880 Berlin 0
40921 Berlin 66
40921 Berlin 101
40921 Berlin 114
40921 Berlin 108
Now I need to find out how to replace this 0 with a blank ... -
GOT IT !
If I run following query, it's all fine ...
select ccc_id, town, '*' + rtrim(TOWN) + '*',
'*' + replace(rtrim(town), char(0), '') + '*'
from CLI_CONTACT_CHANGES
where FK_CLIENT_CONTACLC_ID = '244_CORRESP'
Thanks a lot Luis for your help!!! Problem quiclkly solved
-
I'm glad that you were able to solve your problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply