Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MSSQL - Change Delimiters

    I'm trying to achieve the following, currently caught around the axle. I'm using MS SQL SERVER 2008 R2 and have a nvarchar(255) column with the following data:

    1;#AAA;#2;#BBB;#3;#CCC

    This could continue with #4, #5 etc so no bounds to this, only the field size.

    My objective is to have the query results displaying

    AAA, BBB, CCC, DDD, ..........

    Thanks in Advance

    JR

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,184
    Thanks
    80
    Thanked 4,451 Times in 4,416 Posts
    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(field,'#',''),';',','),'0',''),'1',''),'2','').... AS altered

    maybe??

    It's ugly, but it should work.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    my effort

    Thanks, this is what I created

    select REPLACE(replace
    (replace(replace
    (replace(replace
    (replace(replace
    (replace(replace(field,'1;#',''),';#2;#',', '),
    ';#3;#',', '),';#4;#',', '),';#5;#',', '),';#6;#',', ')
    ,';#7;#',', '),';#8;#',', '),';#9;#',', '),';#10;#',', ') AS fieldclean

    from table
    where field Is not Null

    //JR


  •  

    Posting Permissions

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