March 28, 2017 at 7:32 am
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
March 28, 2017 at 7:50 am
rot-717018 - Tuesday, March 28, 2017 7:32 AMHi 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
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;
March 28, 2017 at 8:11 am
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 ...
March 28, 2017 at 8:18 am
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 🙂
March 28, 2017 at 11:31 am
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