missing concatenated star

  • petitpere

    SSCrazy

    Points: 2520

    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

  • Luis Cazares

    SSC Guru

    Points: 183524

    rot-717018 - Tuesday, March 28, 2017 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

    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;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • petitpere

    SSCrazy

    Points: 2520

    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 ...

  • petitpere

    SSCrazy

    Points: 2520

    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 🙂

  • Luis Cazares

    SSC Guru

    Points: 183524

    I'm glad that you were able to solve your problem.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply