Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forgot function (SQL Server)

    Hello everybody!
    I ran into dilemma.
    I have a string like this:
    lot/1234, lot/3454, lot/34/ lot/456789

    I need to have
    1234
    3454
    34
    456789

    So I need some way of cutting out asi/ and take everything before ","
    I do not know what function will do both.

    Thanks so much for reading.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forgot function (SQL Server)

    Hi there

    I am <img src=/S/confused.gif border=0 alt=confused width=15 height=20>..but that is normal <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Have you tried:

    For SQL Server

    SELECT SUBSTRING(myField,5,datalength(myField)-4)

    VBA you can use:

    Mid(myField, 5, len(myField)-5)

    or Right (MyField, len(myField)-4)
    Jerry

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Forgot function (SQL Server)

    <P ID="edit" class=small>(Edited by jscher2000 on 18-Sep-06 13:39. )</P>Let me assume that the "real" data always has a , between records. I prefer to use "split" for these types of operations.

    <code>dim strArray() as string
    strArray = split(mybigstring, ", ")</code>

    You then have items strArray(0) ... strArray(3) in the array, you can extract the parts you want using a variety of methods. I'm not aware of any automatic way to do it all in one step.

    Edit: If this is to be done in a query or stored procedure, I don't know whether this option is available. Sorry!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Forgot function (SQL Server)

    In addition to my answer could you use this option to remove

    lot/1234, lot/3454, lot/34, lot/456789

    SELECT Replace(MyString,' lot/', ' ')

    to get

    1234, 3545, 34, 456789

    and with the same method that Jeff mentions try it out in a split function procedure, there are some great examples on the web
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forgot function (SQL Server)

    I appreciate everyone's help, i got it but now i have more complicated issue i do not even know how to describe :-)))))

Posting Permissions

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