Select text starting from a predetermined position

[digitallyskilled]

I need to retrieve text from a field like this

<Name> DataIneed </Name>

How can i do this in SQL 2005?

 

[DotNetGnat]

patindex()

-DNG

 

[vongrunt]

Does that field contain XML?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

PATINDEX or CHARINDEX can be used to get a starting and/or end point. SUBSTRING can be used to get the portion you want.

Look up those in the BOL.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLDenis]

Here is an example

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Name>DataIneed</Name>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT',2)
WITH ([Name] varchar(10))

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

If it's not XML and only stored as VARCHAR, this will work

DECLARE @mystring VARCHAR(25)
SET @mystring = '<Name> DataIneed </Name>'
SELECT SUBSTRING(@mystring, (PATINDEX('%>%', @mystring)+1),
          (PATINDEX('%</%', @mystring))- (PATINDEX('%>%', @mystring)+1))

-SQLBill


Posting advice: FAQ481-4875

 

[digitallyskilled]

The data is of type varchar but the data i need is not the same for every record. I would need it to say get me all the data from all the rows between <name> and </name>

 

[SQLBill]

Based on the example you provided, my script should work for you, but it might help if you gave us some examples of what your data really looks like and what you want returned.

-SQLBill

Posting advice: FAQ481-4875