Post reply

CharIndex Help

  • August 4, 2008 at 10:30 am

    #190445

    Help, I just learned about this charindex function but I can't seem to get it to work.

    I have a name field 'Jimenez, Laura @' and I need to make 2 columns one with the first name and one with the last name and ignore all symbols attached to the name. I got part of it to work but don't know enough to finish it

    SUBSTRING(TPSAptfor,(CHARINDEX(',',TPSAptfor,1)+1),???) For the first name

    SUBSTRING(TPSAptfor,1,CHARINDEX(',',TPSAptfor,1)-1) get errors for the last name

    Thanks for any help

  • August 4, 2008 at 11:08 am

    #852795

    First of all when trouble shooting simplify the code, (Since you need to find the 1st and Last character positions for the substring function) so lets find them. For example:

    DECLARE @Tpsaptfor AS VARCHAR(50)

    DECLARE @I as int

    DECLARE @j-2 AS INT

    DECLARE @FirstName as VARCHAR(50)

    DECLARE @LastName AS VARCHAR(50)

    SET @Tpsaptfor = 'Jimenez, Laura @'

    SET @I = CHARINDEX(',',@TPSAptfor,1)

    SET @j-2 = CHARINDEX('@',@Tpsaptfor,@I +1)

    SET @FirstName = LTRIM(RTRIM(SUBSTRING(@Tpsaptfor,@I+1,(@J-1)-@I)))

    SET @LastName = LTRIM(RTRIM(SUBSTRING(@Tpsaptfor,1,@I-1)))

    SELECT @I, @j-2,@Firstname,@LastName

    This will give you:

    For @I @j-2 First Last

    8 17 Laura Jimenez

    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]

  • August 4, 2008 at 11:29 am

    #852804

    Thanks...I am getting this error:

    Invalid length parameter passed to the SUBSTRING function.

  • August 4, 2008 at 11:41 am

    #852818

    This, "SUBSTRING(TPSAptfor,1,CHARINDEX(',',TPSAptfor,1)-1)" will give you an invalid substring length error (which is the one you seem to be getting) if there is no comma in the field.

    Select the rows where there is a comma:

    Where CHARINDEX(',',TPSAptfor,1) > 0

    And it should work.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 4, 2008 at 12:13 pm

    #852847

    Thanks a bunch worked great

Viewing 5 posts - 1 through 4 (of 4 total)

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