Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    14

    Unanswered: Need help with String modification

    Im trying to basically take an email address user@domain.com that is in a row and basically split it up or get 1 side.

    Im trying to find the command if it exists or a way to do this in tran sql to basically give me everything left or right of the '@' symbol, is there an easy way of doing this? Thanks!

    Chris

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select SUBSTRING(column, 1, charIndex('@', column)-1) from table
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Location
    Cupertino, CA
    Posts
    10
    Hi Everyone,

    I too have a similar question. I have a column in which the data is in the format (270185)-(3706) According to your solution I have used

    SUBSTRING(column, 1, charIndex('-', column)-1)
    and I got the value (270185).
    Now my question is how to remove the '(braces)'. Actually I want to separate the column into two different columns i.e 270185 and 3706. I would appreciate if you could throw some light on this.

    Thanks in advance.

    kalyan

  4. #4
    Join Date
    Feb 2004
    Posts
    14
    sweet thanks! Also kalp you can use

    select replace((replace('(test)','(','')),')','')

    nest 2 replace calls to get your results.

  5. #5
    Join Date
    Feb 2004
    Posts
    14
    actually to do your results you would do an insert into

    insert into newtable(newcol1,newcol2)
    select replace((replace((substring('(5654)-(5342)',1,charindex('-','(5654)-(5342)')-1)),'(','')),')','') , replace((replace((substring('(5654)-(5342)',charindex('-','(5654)-(5342)')+1,50)),'(','')),')','')


    replace the (5454)-(4543) numbers of course with your column names and this will give the results

  6. #6
    Join Date
    Jan 2004
    Location
    Cupertino, CA
    Posts
    10
    Hi Turismon,

    Thanks for the reply. Moreover I figured out this:

    SUBSTRING(Ship_To_AB_Number, 2, charIndex(')', Ship_To_AB_Number)-2)

    Select SUBSTRING(Ship_To_AB_Number, 11, len(Ship_To_AB_Number)-11)


    since the format is: (270185)-(xxxxx) and the first value in braces is always 6 in length. the second value in the braces varies.

    Thankyou.

    Regards,
    kalyan

  7. #7
    Join Date
    Feb 2004
    Posts
    14
    Hmm, thats with static text, not sure how it would react on a shared table, might want to test it with a few numbers and see what happends, if im wrong here, them you could always do the substring into the separate columns first, then do the replace afterwards.

    but also if you need these in order with the other one etc, then its a whole nother ball game where you will have to match the data with a customer number or something etc etc, but anyway you have the syntax on how to do it now you just gotta figure out the manipulation :P.

  8. #8
    Join Date
    Feb 2004
    Posts
    14
    well if you use the replace method for the 2nd part to get rid of the (,)'s the subquery can just be set to anything after the - and just put in a number that you know wont be as long, so like i have in there 50, doubt any of your numbers go 50 long, and this will basically get everything up to 50 after the -

Posting Permissions

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