Post reply

need to remove a pipe in a part number

  • July 9, 2007 at 1:54 pm

    #173440

    I have a part with a pipe in it and a number, (e.g.12345|03).

    I need to remove the pipe and the number to the right of it, but the number varies in length.

    What is the function that will pull it out??  I need a substring, but one that looks for the | pipe.

    Thanks

     

    -k

  • July 9, 2007 at 2:02 pm

    #718098

    You can use PATINDEX to find the location of the pipe and then use that in the substring function. 

  • July 9, 2007 at 2:03 pm

    #718099

    DECLARE @tbl TABLE(PartNo varchar(20))

    INSERT INTO @tbl VALUES('12345|03')

    SELECT SUBSTRING(PartNo, 1, CHARINDEX('|', PartNo)-1)

    FROM @tbl

    WHERE  CHARINDEX('|', PartNo) > 0

  • July 9, 2007 at 3:06 pm

    #718116

    or:

    DECLARE @tbl TABLE(PartNo varchar(20))

    INSERT INTO @tbl VALUES('12345|03')

    SELECT REPLACE(PartNo, '|','')

    FROM @tbl

    WHERE CHARINDEX('|', PartNo) > 0


    * Noel

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

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