How to replace first occurrance of pipe delimeter

Question

How to replace first occurrance of pipe delimeter

tan110

Hall of Fame

Points: 3855
More actions
November 16, 2010 at 3:43 pm

#97776

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
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]

tan110 Hall of Fame Points: 3855 More actions · Answer 1

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.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

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/

tan110 Hall of Fame Points: 3855 More actions · Answer 3

tan110

Hall of Fame

Points: 3855

November 17, 2010 at 6:01 am

#1251846

Thank you very much! Really appreciate it.

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 4

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

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);

Stephen.Richardson Right there with Babe Points: 758 More actions · Answer 6

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*

WayneS SSC Guru Points: 95461 More actions · Answer 7

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

GSquared SSC Guru Points: 260824 More actions · Answer 8

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

How to replace first occurrance of pipe delimeter

Cookies on SQLServerCentral