Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Extracting substring text within SQL Server


  • Please log in to reply

#1
coyne20

coyne20

    Member

  • Member
  • PipPip
  • 35 posts
Does any one know on how to extract text/string within a sql server table column that is embedded in between XML like braces?

eg. Column Name: ActivityDefinitionXML

Data in the column: ...<Reason for Suspension>This is a test</Reason for Suspension>...

So for example I will need to retrieve "This is a test" text from in between the xml braces. I am familiar with the substring function however that is only use for fixed position indexing of characters within the column. Suppose if it is not a fixed indexed column. Is there any SQL out there that does it?

Any help would be greatly appreciated.
  • 0

Advertisements


#2
Father0fNine

Father0fNine

    Member

  • Member
  • PipPip
  • 29 posts
It pretty much has to be a two step process if you don't have an McAdd-ons... First step is to find where your bracketed text begins and ends and then use SUBSTRING to extract the data. Something like (I left the code expanded with comments to describe what's going on):

DECLARE @Input varchar(max)
DECLARE @Result varchar(max)
DECLARE @Open int
DECLARE @Close int

--					 1		 2		 3		 4		 5		 6
--			1234567890123456789012345678901234567890123456789012345678901
SET @Input = '<Reason for Suspension>This is a test</Reason for Suspension>'
SET @Open = PATINDEX( '%>%', @Input )+1  --location of first >.  Move right for first data character
SET @Close = PATINDEX( '%</%', @Input )-1 --locate the start of the ending tag. Move left for last data
SET @Result = SubString( @Input, @Open, @[email protected]+1 )

PRINT @Open
PRINT @Close
PRINT @Result

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP