Post reply

Question

  • September 10, 2008 at 8:40 am

    #212431

    Hey all,

    I have a bunch of fields with lastname,first name in it.

    I am trying to take first name place it in a new column named firstname then do the same with lastname.

    The problem I have is I need the script to find what value '"," is in so that I can select substring(name,1,",") if that makes any sense lol....

  • September 10, 2008 at 8:47 am

    #869714

    CHARINDEX is what you are after:-

    select substring(name,1,charindex(',',name)-1)

  • September 10, 2008 at 8:56 am

    #869721

    The charindex function should do what you want and it can be placed inside of the substring function so its results get passed directly to the substring function.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • September 10, 2008 at 8:59 am

    #869724

    How funny....

    That is what i was messing with while waiting for replies....

    select charindex(',',name,1)

    from WritersNames

    The odd part, some of the values return as ZERO....:ermm:

  • September 10, 2008 at 9:01 am

    #869728

    Ian Scarlett (9/10/2008)

    CHARINDEX is what you are after:-

    select substring(name,1,charindex(',',name)-1)

    select substring(name,1,charindex(',',name,1))

    I think this is what you meant?

  • September 10, 2008 at 9:13 am

    #869739

    --Not very elegant but I hope this helps cut, copy the whole lot into

    -- query analyzer to test

    --I made a test1 table to hold a fullname

    create table test1

    (fullname varchar(80) null)

    --inserted a test row

    insert into test1(fullname)

    values ('Sharon, Smith')

    -- this selects the fullname to check it, then forename then surname

    -- I used charindex to find where in the string ',' ocurred (7 in example)

    -- then I used the result - 1 for the end point of a substring (forename)

    -- result +2 ( for the space aswell) for start point of surname

    select fullname, substring(fullname,1,charindex(',', fullname)-1), substring(fullname, charindex(',', fullname) +2, 100)

    from test1

  • September 10, 2008 at 9:16 am

    #869742

    CharIndex returns a zero if the search string is not there. I'd check your data to see if they all have a comma in.

  • September 10, 2008 at 9:26 am

    #869750

    This also seemed to work also...

    SELECT LEFT( name , CHARINDEX( ' ', name,-1 ))

    from WritersNames

    SELECT RIGHT( name , CHARINDEX( ' ',reverse(name)))

    from WritersNames

  • September 10, 2008 at 9:35 am

    #869762

    Also, it doesn't seem to like the -1 length outside of the charindex statement..

  • September 10, 2008 at 3:17 pm

    #869995

    select substring(name,1,charindex(',',name,1))

    I think this is what you meant?

    No, I meant what I posted.

    charindex(',',name) will give you the position of the comma, and the -1 backs it up by one to exclude the comma.

    Try this:-

    declare @name varchar(50)

    set @name = 'Smith, Fred'

    --this will return 'Smith'

    select substring(@name,1,charindex(',',@name)-1)

    --this will return 'Smith, '

    select substring(@name,1,charindex(',',@name)+1)

  • September 11, 2008 at 7:19 pm

    #870724

    yea, the string functions in T-SQL are not vast, but if you work them, they can do the trick.

    The more you are prepared, the less you need it.

  • September 11, 2008 at 9:20 pm

    #870732

    Believe this will handle both "Jones, Jack" and "Jones Jack" equally as well.

    DECLARE @WritersNames AS VARCHAR(50)

    DECLARE @Pos AS INT

    SET @WritersNames = 'Jones Jack'

    SET @Pos = (Select charindex(',',@Writersnames,1))

    IF (@Pos = 0) -- Blank between names

    BEGIN

    select substring(@Writersnames,1,charindex(' ',@Writersnames,1))

    END

    ELSE -- Comma between names

    BEGIN

    select substring(@Writersnames,1,charindex(',',@Writersnames,1)-1)

    END

    Not shown, but other items to consider.

    Combination of "Jones, Jack" - comma followed by 2 blanks

    "Jones,Jack" - comma without following blank

    "Jones Jack" -- no comma multiple blanks can use LTRIM to trim leading blanks ..

    You should have enough to handle all of the above, so will leave it to you.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • September 14, 2008 at 3:39 am

    #871523

    declare @name varchar(50)

    set @name = 'Smith,Fred,John,J.Smith,Ann,Mary,Ken,Ted,quen,Kin'

    What about separating more names?

    Will the same query work?

  • September 14, 2008 at 9:07 am

    #871548

    To expand beyond the simple parsing of Last name, first name to what you have asked may I recommend reading an excellent article by Steve Jones at http://qa.sqlservercentral.com/articles/Advanced+Querying/20010422115807/91/

    Also consider Steve's statement in the above article

    After all, shouldn't the validation be occurring in the presentation or business layers?

    as the true solution.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

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