substring issue

Question

Post reply

substring issue

timscronin

SSCertifiable

Points: 6851
More actions
March 5, 2013 at 2:55 pm

#290662

Not sure what I am missing here possible brain freeze
declare @loginname varchar(35)
set @loginname = '<rxno>001c</rxno>\test1'
select SUBSTRING(@loginname,CHARINDEX('<rxno>',@loginname)+1,CHARINDEX('</rxno>',@loginname)-CHARINDEX('rxno>',@loginname)-1)
I want to return only 001c but I am getting rxno>001

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

This:

declare @loginname varchar(35)

set @loginname = '<rxno>001c</rxno>\test1'

select SUBSTRING(@loginname,CHARINDEX('<rxno>',@loginname)+6,CHARINDEX('</rxno>',@loginname)-CHARINDEX('rxno>',@loginname)-5)

manole_i SSC Eights! Points: 860 More actions · Answer 2

or something like:

declare @loginname varchar(35), @xml xml

set @loginname = '<rxno>001c</rxno>\test1'

set @xml = convert(xml, @loginname)

select @xml.value('(/rxno)[1]', 'varchar(4)')

the value method is used to perform an Xquery againts a XML instance to fetch a single scalar value.

http://msdn.microsoft.com/en-us/library/ms178030.aspx

buddy__a Hall of Fame Points: 3222 More actions · Answer 3

Just wanted to explain why you were getting what you were getting. CHARINDEX finds the STARTING position of the string you specify to be found (not the ending position). So, it finds your string at position 1 then you add 1 to it so SUBSTRING gets the string starting at position 2.

Given that you're suing an XML string I'd use XML methods to get the desired output.