CODEWITHSUNDEEP
sqlsql-server-2008
Shankaranand
Shankaranand

Reputation: 67

Get Last values using Select Substring in SQL Query

I have a table with following Column

Name
----------------------
test10/20000020                     
test1/test2 / 20000001
test3/test4 / 20000002
test5/20000017
test5/test6 / 20000004
test5/20000007

I need to select the last value I.e

20000020
20000001
20000002
20000017
20000004
20000007

I have tried with

(SUBSTRING(Name,0,(CHARINDEX('/',Name,0))))

But I am getting First Value i.e. test1 test2 test3

Upvotes: 1

Views: 134

Answers (4)

Er Ketan Vavadiya
Er Ketan Vavadiya

Reputation: 283

You could solve your problem with the following query:

SELECT (SUBSTRING(Name,(CHARINDEX('/',Name,0)),LEN(Name)))

Upvotes: 0

Shankaranand
Shankaranand

Reputation: 67

SELECT SUBSTRING( Name, LEN(Name) - CHARINDEX('/',REVERSE(Name)) + 2 , LEN(Name) ) FROM SAMPLE

worked for me

Upvotes: 1

Andrei Bucurei
Andrei Bucurei

Reputation: 2438

Using XML:

declare @xml xml;
declare @str nvarchar(max) = '';
with data as
(
  select 'test1/test2/test3/344495' as r
  union ALL
  select 'test1/344556' as r
)
select @str = @str + '<r><v>' + replace(r,'/','</v><v>') + '</v></r>'
from data;
-- obtain xml
set @xml = cast(@str as xml);

-- select last value from each row
select v.value('(v/text())[last()]', 'nvarchar(50)')
from @xml.nodes('/r') as r(v)

Same idea but without variables:

;with data as
(
  select 'test1/test2/test3/344495' as r
  union ALL
  select 'test1/344556' as r
),
xmlRows AS
(
  select cast('<r><v>' + replace(r,'/','</v><v>') + '</v></r>' as xml) as r
  from data
)
select v.value('(v/text())[last()]', 'nvarchar(50)') as lastValue
from   xmlRows xr
       cross APPLY
       r.nodes('/r') as r(v)

Upvotes: 1

pikk
pikk

Reputation: 525

SUBSTRING ( expression ,start , length )

Instead of that 

(SUBSTRING(Name,0,(CHARINDEX('/',Name,0))))

You should use

(SUBSTRING(Name,(CHARINDEX('/',Name,0)+1),8))

if string size is fixed.

Upvotes: 0

Related Questions