substring issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » substring issue

substring issue

Rate Topic

Display Mode

Topic Options

Author

Message

timscronin

Posted Tuesday, March 05, 2013 2:55 PM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:54 AM
Points: 472, Visits: 686

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

Post #1427048

Lynn Pettis

Posted Tuesday, March 05, 2013 3:20 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 21,602, Visits: 27,428

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)

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1427054

manole_i

Posted Tuesday, March 05, 2013 3:43 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 28, 2013 12:21 PM
Points: 10, Visits: 243

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

Post #1427064

buddy__a

Posted Wednesday, March 06, 2013 10:28 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:33 PM
Points: 303, Visits: 167

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.

Post #1427525

« Prev Topic | Next Topic »

Permissions