Post reply

INSERT A Hyphen

  • October 22, 2007 at 6:01 pm

    #176412

    I have the following data in a column

    PrefNum

    1289356

    3562198

    5631249

    I need to INSERT a Hyphen ('-') after the first digit in each row. How to do?

    Example,

    1-289356

    3-562198

    5-631249

    Kindest Regards,

  • October 22, 2007 at 6:27 pm

    #744281

    Folks,

    It was rather easy in the end. I have worked it by SUBSTRING.

    Kindest Regards,

  • October 22, 2007 at 9:13 pm

    #744306

    It's easier than that... lookup STUFF in Books Online...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 24, 2007 at 10:04 am

    #745031

    Jeff Moden (10/22/2007)

    It's easier than that... lookup STUFF in Books Online...

    Cool! I hadn't encountered that function before. Thanks Jeff.

  • October 24, 2007 at 7:13 pm

    #745177

    You bet... thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 26, 2007 at 10:16 am

    #745814

    Jeff Moden (10/22/2007)

    It's easier than that... lookup STUFF in Books Online...

    I was curious how you would do this with the STUFF function. I tried testing it with the following:

    SELECT STUFF('123456', 2, 1, '-') = 1-3456

    SELECT STUFF(1289356, 2, 1, '-') = 1-89356

    But, STUFF deletes the characters and replaces it with the new characters. Wouldn't I have to use a SUBSTRING anyway to maintain the same characters and just add the hyphen? As follows:

    SELECT STUFF('123456', 1, 1, SUBSTRING('123456',1,1) + '-') = 1-23456

    SELECT STUFF('1289356', 1, 1, SUBSTRING('1289356',1,1) + '-') = 1-289356

    I may be missing something here though, if you can help me understand the easier way that would be great. Thanks.

  • October 26, 2007 at 10:23 am

    #745818

    John Dempsey (10/26/2007)

    I was curious how you would do this with the STUFF function. I tried testing it with the following:

    SELECT STUFF('123456', 2, 1, '-') = 1-3456

    SELECT STUFF(1289356, 2, 1, '-') = 1-89356

    But, STUFF deletes the characters and replaces it with the new characters. Wouldn't I have to use a SUBSTRING anyway to maintain the same characters and just add the hyphen? As follows:

    SELECT STUFF('123456', 1, 1, SUBSTRING('123456',1,1) + '-') = 1-23456

    SELECT STUFF('1289356', 1, 1, SUBSTRING('1289356',1,1) + '-') = 1-289356

    I may be missing something here though, if you can help me understand the easier way that would be great. Thanks.

    SELECT STUFF('123456', 2, 0, '-') = 1-23456

    SELECT STUFF(1289356, 2, 0, '-') = 1-289356

  • October 26, 2007 at 11:47 am

    #745857

    Nice! It is amazing what a 0 can do. Thanks for the quick response.

  • October 26, 2007 at 12:02 pm

    #745869

    You're welcome.

    Julie

Viewing 9 posts - 1 through 8 (of 8 total)

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