display last 18 numbers of instant numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

display last 18 numbers of instant numbers

Author

Topic

djamit
Starting Member

17 Posts

Posted - 2014-02-21 : 06:34:05

Hi SQL team,

I have a column where instant numbers are imported. The numbers are like
1.2.840.10008.5.1.4.1.1.481.5.3020140221.75119.101

I only need the last 18 numbers (3020140221.75119.101) in the column.

How can I solve this?

Thanks,

Kind regards,

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-21 : 06:58:43

the pattern is the same , always?


declare @vcNumber as varchar(500)
		,@iNoPoints as int
		,@iNoChars as int

set @vcNumber='1.2.840.10008.5.1.4.1.1.481.5.3020140221.75119.101'
set @iNoPoints=2
set @iNoChars=18

select right(@vcNumber, @INoChars + @iNoPoints)

sabinWeb MCP

djamit
Starting Member

17 Posts

Posted - 2014-02-21 : 07:03:50

Hello Stepson,

Yes the pattern is the same always but the numbers are differnet for each record.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-21 : 08:14:13

[code]

declare @vcNumber as varchar(500)
,@iNoPoints as int
,@iNoChars as int
,@xml as xml

set @vcNumber='1.2.840.10008.5.1.4.1.1.481.5.3020140221.75119.101'
set @iNoPoints=2
set @iNoChars=18

select @xml='<Numbers><numb>' +
replace(@vcNumber,'.','</numb><numb>') + '</numb></Numbers>'

select
t.u.value('numb[12]','varchar(50)') + '.' + t.u.value('numb[13]','varchar(50)') + '.' + t.u.value('numb[14]','varchar(50)')
from @xml.nodes('Numbers')t(u)

[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-21 : 08:29:18

[code]

;with aCTE
AS (
select 1 [ID],'1.2.840.10008.5.1.4.1.1.481.5.3020140221.75119.101' as InitCol union all
select 2,'3.2.840.10308.5.1.4.1.1.481.5.33333020140221.3333375119.103'
)

,bCTE
AS
(select ID, CAST('<Numbers><numb>' +replace(InitCol,'.','</numb><numb>') + '</numb></Numbers>' as XML) as FinalCol
from aCTE )

select
t.u.value('numb[12]','varchar(50)') + '.' + t.u.value('numb[13]','varchar(50)') + '.' + t.u.value('numb[14]','varchar(50)')
from bCTE b
CROSS APPLY
b.FinalCol.nodes('Numbers') as t(u)

[/code]

S

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources