Professional Web Applications Themes

CHAR(0) within a string - Microsoft SQL / MS SQL Server

Should it be possible to have a string that contains CHAR(0) at any point within a string? I have head that CHAR(0) is used by some languages to truncate strings to that point, however I am far from certain about the validity of this statement. I had a problem with some data this morning that contained 3 CHAR(0) values at the start of a particular column. Needless to say, the website that was attempting to display this string did not handle the problem very well. More interestingly was the fact that QA (7.00.623) using text output had issues displaying the ...

  1. #1

    Default CHAR(0) within a string

    Should it be possible to have a string that contains CHAR(0) at any point within a string?

    I have head that CHAR(0) is used by some languages to truncate strings to that point, however I am far from certain about the validity of this statement.

    I had a problem with some data this morning that contained 3 CHAR(0) values at the start of a particular column. Needless to say, the website that was attempting to display this string did not handle the problem very well.
    More interestingly was the fact that QA (7.00.623) using text output had issues displaying the data also, as per the following examples.


    /* SELECTING COLUMN ON ITS OWN */
    SELECT
    e.headline
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    -------------------------------------------------------------------------------------------------------


    (1 row(s) affected)


    /* SELECTING COLUMN WITH ANOTHER COLUMN OR CONSTANT */
    SELECT
    e.headline
    , 1
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    ------------------------------------------------------------------------------------------------------- -----------
    Rain douses Charters sale 1

    (1 row(s) affected)



    Based on the above, Should QA be able to display the contents of the column in the results pane?



    Just for confirmation that there were indeed char(0) values, I ran the following:

    /* PARSING INDIVIDUAL CHARACTERS FROM COLUMN */
    SELECT
    n.number
    , SUBSTRING( e.headline , n.number , 1 )
    , ASCII( SUBSTRING( e.headline , n.number , 1 ) )
    FROM editorial AS e
    INNER JOIN numeric_sequence AS n
    ON n.number BETWEEN 1 AND LEN( e.headline )
    WHERE e.editorial_id = 53740


    number
    ----------- ---- -----------
    1 0
    2 0
    3 0
    4 R 82
    5 a 97
    6 i 105
    7 n 110
    8 32
    9 32
    10 d 100
    11 o 111
    12 u 117
    13 s 115
    14 e 101
    15 s 115
    16 32
    17 C 67
    18 h 104
    19 a 97
    20 r 114
    21 t 116
    22 e 101
    23 r 114
    24 s 115
    25 32
    26 s 115
    27 a 97
    28 l 108
    29 e 101



    ----------------------------------------

    Damien Laffan

    Rural Press Interactive
    9 Pirie St, Fyshwick
    ACT 2609 Australia

    PH 02 6280 2452
    FAX 02 6280 2378


    Damien Guest

  2. #2

    Default Re: CHAR(0) within a string

    If so, why does QA display the full string as soon as there is following column in the select list?

    SELECT CHAR(0) + 'ABC'
    SELECT CHAR(0) + 'ABC' , 'COL2'

    Also note that the LEN function counts all the characters in the string, regardless of where the CHAR(0) occurs

    SELECT
    a
    , LEN( a ) AS LEN_
    , DATALENGTH( a ) AS DATALEN_
    FROM (
    SELECT CHAR(0) + 'ABC'
    UNION
    SELECT 'DEF' + CHAR(0)
    UNION
    SELECT 'GHI' + CHAR(0) + 'JKL'
    ) AS d ( a )

    Note that this is using text output in QA.


    I think part of my confusion comes from thinking that CHAR(0) is used for SQL NULL, which it does not appear to be:
    DECLARE str CHAR(1)
    SET str = CHAR(0)
    SELECT COALESCE( str , '-' )




    "SQL Server Development Team [MSFT]" <com> wrote in message news:ewhCM$phx.gbl...
    Yes, CHAR(0) does in fact make the end of a SQL Server string. QA will stop
    displaying characters when it hits a CHAR(0) so your string with CHAR()) as
    the first character was a zero-length string. Depending on what your data
    looks like, REPLACE might be what you're looking for:

    create table testzero (string varchar(20))
    insert into testzero values (CHAR(0) + 'More Stuff')
    select * from testzero
    select cast(string as varbinary) from testzero
    select replace (string, CHAR(0),'') from testzero




    Thanks,
    Roger Wolter


    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm.

    "Damien Laffan" <com> wrote in message
    news:phx.gbl...
    Should it be possible to have a string that contains CHAR(0) at any point
    within a string?

    I have head that CHAR(0) is used by some languages to truncate strings to
    that point, however I am far from certain about the validity of this
    statement.

    I had a problem with some data this morning that contained 3 CHAR(0) values
    at the start of a particular column. Needless to say, the website that was
    attempting to display this string did not handle the problem very well.
    More interestingly was the fact that QA (7.00.623) using text output had
    issues displaying the data also, as per the following examples.


    /* SELECTING COLUMN ON ITS OWN */
    SELECT
    e.headline
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    ----------------------------------------------------------------------------
    ---------------------------


    (1 row(s) affected)


    /* SELECTING COLUMN WITH ANOTHER COLUMN OR CONSTANT */
    SELECT
    e.headline
    , 1
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    ----------------------------------------------------------------------------
    --------------------------- -----------
    Rain douses Charters sale
    1

    (1 row(s) affected)



    Based on the above, Should QA be able to display the contents of the column
    in the results pane?



    Just for confirmation that there were indeed char(0) values, I ran the
    following:

    /* PARSING INDIVIDUAL CHARACTERS FROM COLUMN */
    SELECT
    n.number
    , SUBSTRING( e.headline , n.number , 1 )
    , ASCII( SUBSTRING( e.headline , n.number , 1 ) )
    FROM editorial AS e
    INNER JOIN numeric_sequence AS n
    ON n.number BETWEEN 1 AND LEN( e.headline )
    WHERE e.editorial_id = 53740


    number
    ----------- ---- -----------
    1 0
    2 0
    3 0
    4 R 82
    5 a 97
    6 i 105
    7 n 110
    8 32
    9 32
    10 d 100
    11 o 111
    12 u 117
    13 s 115
    14 e 101
    15 s 115
    16 32
    17 C 67
    18 h 104
    19 a 97
    20 r 114
    21 t 116
    22 e 101
    23 r 114
    24 s 115
    25 32
    26 s 115
    27 a 97
    28 l 108
    29 e 101



    ----------------------------------------

    Damien Laffan

    Rural Press Interactive
    9 Pirie St, Fyshwick
    ACT 2609 Australia

    PH 02 6280 2452
    FAX 02 6280 2378




    Damien Guest

  3. #3

    Default Re: CHAR(0) within a string

    My mistake - I thought you had an issue you needed a solution for - not just
    an intellectual curiosity. You had the right answer in your initial post.
    Most languages use a char(0) to mark the end of a string so most of the time
    when you attempt to display the text, it will stop at the char(0). LEN
    returns the number of characters in the column not the display length as you
    found.

    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm.



    "Damien Laffan" <com> wrote in message
    news:%23IPbui%phx.gbl...
    If so, why does QA display the full string as soon as there is following
    column in the select list?

    SELECT CHAR(0) + 'ABC'
    SELECT CHAR(0) + 'ABC' , 'COL2'

    Also note that the LEN function counts all the characters in the string,
    regardless of where the CHAR(0) occurs

    SELECT
    a
    , LEN( a ) AS LEN_
    , DATALENGTH( a ) AS DATALEN_
    FROM (
    SELECT CHAR(0) + 'ABC'
    UNION
    SELECT 'DEF' + CHAR(0)
    UNION
    SELECT 'GHI' + CHAR(0) + 'JKL'
    ) AS d ( a )

    Note that this is using text output in QA.


    I think part of my confusion comes from thinking that CHAR(0) is used for
    SQL NULL, which it does not appear to be:
    DECLARE str CHAR(1)
    SET str = CHAR(0)
    SELECT COALESCE( str , '-' )




    "SQL Server Development Team [MSFT]" <com> wrote in message
    news:ewhCM$phx.gbl...
    Yes, CHAR(0) does in fact make the end of a SQL Server string. QA will stop
    displaying characters when it hits a CHAR(0) so your string with CHAR()) as
    the first character was a zero-length string. Depending on what your data
    looks like, REPLACE might be what you're looking for:

    create table testzero (string varchar(20))
    insert into testzero values (CHAR(0) + 'More Stuff')
    select * from testzero
    select cast(string as varbinary) from testzero
    select replace (string, CHAR(0),'') from testzero




    Thanks,
    Roger Wolter


    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm.

    "Damien Laffan" <com> wrote in message
    news:phx.gbl...
    Should it be possible to have a string that contains CHAR(0) at any point
    within a string?

    I have head that CHAR(0) is used by some languages to truncate strings to
    that point, however I am far from certain about the validity of this
    statement.

    I had a problem with some data this morning that contained 3 CHAR(0) values
    at the start of a particular column. Needless to say, the website that was
    attempting to display this string did not handle the problem very well.
    More interestingly was the fact that QA (7.00.623) using text output had
    issues displaying the data also, as per the following examples.


    /* SELECTING COLUMN ON ITS OWN */
    SELECT
    e.headline
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    ----------------------------------------------------------------------------
    ---------------------------


    (1 row(s) affected)


    /* SELECTING COLUMN WITH ANOTHER COLUMN OR CONSTANT */
    SELECT
    e.headline
    , 1
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    ----------------------------------------------------------------------------
    --------------------------- -----------
    Rain douses Charters sale
    1

    (1 row(s) affected)



    Based on the above, Should QA be able to display the contents of the column
    in the results pane?



    Just for confirmation that there were indeed char(0) values, I ran the
    following:

    /* PARSING INDIVIDUAL CHARACTERS FROM COLUMN */
    SELECT
    n.number
    , SUBSTRING( e.headline , n.number , 1 )
    , ASCII( SUBSTRING( e.headline , n.number , 1 ) )
    FROM editorial AS e
    INNER JOIN numeric_sequence AS n
    ON n.number BETWEEN 1 AND LEN( e.headline )
    WHERE e.editorial_id = 53740


    number
    ----------- ---- -----------
    1 0
    2 0
    3 0
    4 R 82
    5 a 97
    6 i 105
    7 n 110
    8 32
    9 32
    10 d 100
    11 o 111
    12 u 117
    13 s 115
    14 e 101
    15 s 115
    16 32
    17 C 67
    18 h 104
    19 a 97
    20 r 114
    21 t 116
    22 e 101
    23 r 114
    24 s 115
    25 32
    26 s 115
    27 a 97
    28 l 108
    29 e 101



    ----------------------------------------

    Damien Laffan

    Rural Press Interactive
    9 Pirie St, Fyshwick
    ACT 2609 Australia

    PH 02 6280 2452
    FAX 02 6280 2378





    SQL Guest

  4. #4

    Default Re: CHAR(0) within a string

    Roger,

    Thanks for the clarification.


    "SQL Server Development Team [MSFT]" <com> wrote in message news:Ox7$phx.gbl...
    My mistake - I thought you had an issue you needed a solution for - not just
    an intellectual curiosity. You had the right answer in your initial post.
    Most languages use a char(0) to mark the end of a string so most of the time
    when you attempt to display the text, it will stop at the char(0). LEN
    returns the number of characters in the column not the display length as you
    found.

    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm.



    "Damien Laffan" <com> wrote in message
    news:%23IPbui%phx.gbl...
    If so, why does QA display the full string as soon as there is following
    column in the select list?

    SELECT CHAR(0) + 'ABC'
    SELECT CHAR(0) + 'ABC' , 'COL2'

    Also note that the LEN function counts all the characters in the string,
    regardless of where the CHAR(0) occurs

    SELECT
    a
    , LEN( a ) AS LEN_
    , DATALENGTH( a ) AS DATALEN_
    FROM (
    SELECT CHAR(0) + 'ABC'
    UNION
    SELECT 'DEF' + CHAR(0)
    UNION
    SELECT 'GHI' + CHAR(0) + 'JKL'
    ) AS d ( a )

    Note that this is using text output in QA.


    I think part of my confusion comes from thinking that CHAR(0) is used for
    SQL NULL, which it does not appear to be:
    DECLARE str CHAR(1)
    SET str = CHAR(0)
    SELECT COALESCE( str , '-' )




    "SQL Server Development Team [MSFT]" <com> wrote in message
    news:ewhCM$phx.gbl...
    Yes, CHAR(0) does in fact make the end of a SQL Server string. QA will stop
    displaying characters when it hits a CHAR(0) so your string with CHAR()) as
    the first character was a zero-length string. Depending on what your data
    looks like, REPLACE might be what you're looking for:

    create table testzero (string varchar(20))
    insert into testzero values (CHAR(0) + 'More Stuff')
    select * from testzero
    select cast(string as varbinary) from testzero
    select replace (string, CHAR(0),'') from testzero




    Thanks,
    Roger Wolter


    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm.

    "Damien Laffan" <com> wrote in message
    news:phx.gbl...
    Should it be possible to have a string that contains CHAR(0) at any point
    within a string?

    I have head that CHAR(0) is used by some languages to truncate strings to
    that point, however I am far from certain about the validity of this
    statement.

    I had a problem with some data this morning that contained 3 CHAR(0) values
    at the start of a particular column. Needless to say, the website that was
    attempting to display this string did not handle the problem very well.
    More interestingly was the fact that QA (7.00.623) using text output had
    issues displaying the data also, as per the following examples.


    /* SELECTING COLUMN ON ITS OWN */
    SELECT
    e.headline
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    ----------------------------------------------------------------------------
    ---------------------------


    (1 row(s) affected)


    /* SELECTING COLUMN WITH ANOTHER COLUMN OR CONSTANT */
    SELECT
    e.headline
    , 1
    FROM editorial AS e
    WHERE e.editorial_id = 53740
    ----------------------------------------------------------------------------
    --------------------------- -----------
    Rain douses Charters sale
    1

    (1 row(s) affected)



    Based on the above, Should QA be able to display the contents of the column
    in the results pane?



    Just for confirmation that there were indeed char(0) values, I ran the
    following:

    /* PARSING INDIVIDUAL CHARACTERS FROM COLUMN */
    SELECT
    n.number
    , SUBSTRING( e.headline , n.number , 1 )
    , ASCII( SUBSTRING( e.headline , n.number , 1 ) )
    FROM editorial AS e
    INNER JOIN numeric_sequence AS n
    ON n.number BETWEEN 1 AND LEN( e.headline )
    WHERE e.editorial_id = 53740


    number
    ----------- ---- -----------
    1 0
    2 0
    3 0
    4 R 82
    5 a 97
    6 i 105
    7 n 110
    8 32
    9 32
    10 d 100
    11 o 111
    12 u 117
    13 s 115
    14 e 101
    15 s 115
    16 32
    17 C 67
    18 h 104
    19 a 97
    20 r 114
    21 t 116
    22 e 101
    23 r 114
    24 s 115
    25 32
    26 s 115
    27 a 97
    28 l 108
    29 e 101



    ----------------------------------------

    Damien Laffan

    Rural Press Interactive
    9 Pirie St, Fyshwick
    ACT 2609 Australia

    PH 02 6280 2452
    FAX 02 6280 2378






    Damien Guest

Similar Threads

  1. Replies: 7
    Last Post: January 8th, 05:19 PM
  2. count char in string
    By W. Paulisse in forum PHP Programming
    Replies: 3
    Last Post: September 24th, 08:12 PM
  3. String formatting function - First char Upper, rest lower
    By James Johnson in forum PHP Development
    Replies: 1
    Last Post: September 6th, 09:48 PM
  4. Best way to process a string 1 char at a time
    By Joshua Ghiloni in forum PHP Development
    Replies: 0
    Last Post: August 20th, 08:21 PM
  5. how to make the last char of a string blink/flash?
    By Gaz Walker webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 4
    Last Post: July 17th, 08:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139