How to replace first occurrance of pipe delimeter

  • I have the following string that starts off with a pipe delimeter:

    | hello | world | I love sql server

    How do I strip off the first | pipe delimeter?

    so that my output is like this:

    hello | world | I love sql server

  • some other records also come in w/o a pipe as the first character, so I don't want to strip off the first character if its not a pipe.

  • You can use the SUBSTRING and LEN function as below..

    DECLARE@strVariable VARCHAR(100)

    SELECT@strVariable = '| hello | world | I love sql server'

    SELECTCASE

    WHEN SUBSTRING( @strVariable, 1, 1 ) = '|'

    THEN SUBSTRING( @strVariable, 2, LEN( @strVariable ) - 1 )

    ELSE @strVariable

    END Variable


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thank you very much! Really appreciate it.

  • For fun 2 slight variations based on Kingston's.

    DECLARE @strVariable VARCHAR(100)

    SELECT @strVariable = '| hello | world | I love sql server'

    SELECT CASE SUBSTRING(@strVariable, 1, 1)

    WHEN '|'

    THEN SUBSTRING(@strVariable, 2, LEN(@strVariable) - 1)

    ELSE @strVariable

    END Variable

    SELECT CASE CHARINDEX('|', @strVariable)

    WHEN 1

    THEN SUBSTRING(@strVariable, 2, LEN(@strVariable) - 1)

    ELSE @strVariable

    END Variable

  • And one more for luck:

    If you are looking to strip a local variable:

    DECLARE @strVariable VARCHAR(100)

    SELECT @strVariable = '| hello | world | I love sql server'

    --== Only strip out the first character if it is a pipe.

    SELECT @strVariable = STUFF(@strVariable,1,1,'')

    WHERE LEFT(@strVariable,1) = '|'

    SELECT @strVariable

    If you are stripping a column in a table:

    --== If the first character is a pipe, stuff it in the bin otherwise don't

    SELECT CASE

    WHEN LEFT(YourColumn,1)='|' THEN STUFF(YourColumn ,1,1,'')

    ELSE YourColumn

    END as StrippedPipe

    FROM YourTable

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • What about a simple "SUBSTRING" & "PATINDEX"

    DECLARE @str1 VARCHAR(100), @str2 VARCHAR(100), @str3 VARCHAR(100)

    SET @str1 = 'hello | world | I love sql server'

    SET @str2 = '| '+@str1

    SET @str3 = '| | | ' + @str1

    SELECT '*'+@str1+'*', '*'+SUBSTRING(@str1,PATINDEX('%[0-z]%', @str1), LEN(@str1))+'*'

    SELECT '*'+@str2+'*', '*'+SUBSTRING(@str2,PATINDEX('%[0-z]%', @str2), LEN(@str2))+'*'

    SELECT '*'+@str3+'*', '*'+SUBSTRING(@str3,PATINDEX('%[0-z]%', @str3), LEN(@str3))+'*'

    *hello | world | I love sql server**hello | world | I love sql server*

    *| hello | world | I love sql server**hello | world | I love sql server*

    *| | | hello | world | I love sql server**hello | world | I love sql server*

  • If it will always be the first two characters that you want removed, just use the STUFF function:

    declare @STR varchar(500);

    set @STR = '| hello | world | I love sql server';

    select stuff(@str, 1, 2, '');

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (12/27/2010)


    If it will always be the first two characters that you want removed, just use the STUFF function:

    declare @STR varchar(500);

    set @STR = '| hello | world | I love sql server';

    select stuff(@str, 1, 2, '');

    And if you want to do that to rows in a table, just add a Where clause that checks "Where MyStringColumn like '|%'".

    - 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

  • Viewing 9 posts - 1 through 9 (of 9 total)

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