Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select text starting from a predetermined position

Status
Not open for further replies.

digitallyskilled

IS-IT--Management
Sep 23, 2004
39
US
I need to retrieve text from a field like this

<Name> DataIneed </Name>

How can i do this in SQL 2005?
 
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)
[banghead]
 
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
 
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:
Personal Blog:
 
If it's not XML and only stored as VARCHAR, this will work
Code:
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
 
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>
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top