March 5, 2013 at 2:55 pm
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
March 5, 2013 at 3:20 pm
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)
March 5, 2013 at 3:43 pm
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.
March 6, 2013 at 10:28 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy