Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
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, @Close-@Open+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