substring function

[plork123]

Hi

Can anyone show me how to do the following:

I have this snippet of data

ids
-----------------------------------
1582270|1582277
1582270|1582277|1582286
1582270

I want to return the id (the id may not always have the same number of numbers) after the last pipe (|) delimter or just the number if it has no pipes

So i want a rs like this :
ids
-----------------------------------
1582277
1582286
1582270

Thanks in advance

 

[SQLBill]

Will the ID always be 7 digits?

-SQLBill

 

[dreameR25]

Your question is a little bit vague to be honest. The second line of your id contains 3 different IDs separated by a pipe so do you want to return the first one or all three?!

anyway, perhaps the following bit of code might give you the inspiration to find a better solution!

declare @test varchar(50)

set @test = '1582270|1582277|1582286'

select case when charindex('|', @test) = 0 then @test else substring(@test, 1, charindex('|', @test)-1)end

 

[SQLBill]

dreamR25,

The poster says that when there are multiple IDs, they only want the last one. Also their example supports that.

-SQLBill

 

[hmckillop]

something similiar

declare @v1 varchar(100)
set @v1= '1582270|1582277'
--SEt @v1='1582270|1582277|1582286'
--SET @v1='1582270'

SELECT CASE  
		WHEN charindex('|', reverse(@v1)) = 0 THEN @v1
		ELSE RIGHT(@v1, charindex('|', reverse(@v1))-1)
		END As LastID

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dreameR25]

Oops!! My mistake... the questio is clear if you read it properly D'oh.

Anyway, hmckillop does the job.

 

[r937]

the last ID is given by:

select case 
        when charindex('|',ids) = 0
        then ids
        else right(ids
               , charindex('|'
                   ,reverse(ids))-1) 
         end as LastID

rudy

http://r937.com/

SQL Consulting

 

[SQLBill]

If the ID is ALWAYS going to be 7 digits in length, this should work:

SELECT RIGHT(IDs,7)
FROM yourtablename

-SQLBill

 

[r937]

oops, simultaneous posting!

rudy

http://r937.com/

SQL Consulting

 

[dreameR25]

no rudy, you just copied it from mine, now give up the little games!

 

[plork123]

thanks dreameR25

 

[r937]

no i didn't!

i copied it from this code snippet which i wrote just the other day:

select left(contact,len(rtrim(contact))-charindex(' ',reverse(rtrim(contact)))) as FirstName
, ltrim(right(rtrim(contact), charindex(' ',reverse(rtrim(contact))))) as LastName

;-)


rudy

http://r937.com/

SQL Consulting

 

[hmckillop]

Guessing that your ids are 7 digits (guessing that its an identity or similiar field type populated from somewhere else), SQlBill's solution is by far the simplest and fastest way of implementing things.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[dreameR25]

Relax rudy, it was a joke dude.

 

[hmckillop]

Huh, he didnt copy dreameR25, he copied mine! ;p

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dreameR25]

The question states,

(the id may not always have the same number of numbers)

 

[plork123]

the ids won't alway be 7 digits


thanks for all the replies

 

[SQLBill]

A

http://WWWWWWWWWW,

just my luck....I point out that dreamR25 missed part of the post and then I go ahead and miss part of it myself.

Dagnabit!

-SQLBill