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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -