June 19, 2014 at 11:29 am
I have the following string and am trying to select only the characters before the last "</>". How can I do this?
declare @String varchar(500)
set @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>'
select REVERSE(substring(REVERSE(@String),5,charindex(':',REVERSE(@String))-5))
Here is what I tried so far:
SELECT SUBSTRING(@String,CHARINDEX('</p>',@String,(CHARINDEX('</p>',@String)+1))-11,
LEN(@String)-CHARINDEX('</p>',@String)-20)
SELECT RIGHT(@String,CHARINDEX('</p>',REVERSE(@String),0)-1)
select LEN(@String)
select charindex('Reviewed By: ',(@String))
select charindex('Reviewed By: ',Reverse(@String)) + datalength('</p>')+1,len(@String)
select SUBSTRING(@String, 61,10)
How can I do this When I run your above query?
home Adress here
June 19, 2014 at 12:21 pm
I'm unsure exactly what your intended output should be, but here's a method using XML which might give you more flexibility. I'm stripping out the Assessed By and Reviewed By headings to get just to the raw data then adding them back afterwards, but again being unsure what your exact goal is hopefully this helps.
DECLARE @table TABLE (XmlContent XML);
DECLARE@E1 VARCHAR(50),
@E2 VARCHAR(50),
@E3 VARCHAR(50),
@String varchar(500);
set @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>';
INSERT INTO @table VALUES('<MessageContent>' + @String + '</MessageContent>');
WITH Tbl (E1, E2, E3) AS (
SELECT
E1 = XmlContent.value('(/MessageContent/p)[1]', 'varchar(50)'),
E2 = XmlContent.value('(/MessageContent/p)[2]', 'varchar(50)'),
E3 = XmlContent.value('(/MessageContent/p)[3]', 'varchar(50)')
FROM @table)
SELECT@E1 = LTRIM(RTRIM(RIGHT(E1,LEN(E1) - CHARINDEX(':',E1)))),
@E2 = LTRIM(RTRIM(RIGHT(E2,LEN(E2) - CHARINDEX(':',E2)))),
@E3 = LTRIM(RTRIM(RIGHT(E3,LEN(E3) - CHARINDEX(':',E3))))
FROMTbl;
SELECT 'Assessed By: ' + @E1;
SELECT 'Reviewed By: ' + @E2;
SELECT @E3;
Take care,
Sam
June 21, 2014 at 8:15 am
If the data sample is accurate, this can be simplified by using XML and XQuery
😎
DECLARE @String nvarchar(500) = N''
SET @String = N'
Assessed By: Michael Jordan Yagnesh
Reviewed By: Fred Smith
Home Address'
DECLARE @XSTR XML = N'<root>' + @String + N'</root>';
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RID
,CASE
WHEN CHARINDEX(':',PA.RT.value('.[1]', 'VARCHAR(50)')) > 0 THEN
LTRIM(SUBSTRING(PA.RT.value('.[1]', 'VARCHAR(50)')
,CHARINDEX(':',PA.RT.value('.[1]', 'VARCHAR(50)')) + 1,100))
ELSE PA.RT.value('.[1]', 'VARCHAR(50)')
END AS PART
FROM @XSTR.nodes('root/p') AS PA(RT);
Results
RID PART
---- -----------------------
1 Michael Jordan Yagnesh
2 Fred Smith
3 Home Address
June 23, 2014 at 8:58 am
Taking a literal interpretation of the words you used, here's the code:
DECLARE @String varchar(500);
SET @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>'
PRINT LEFT(@String, DATALENGTH(@String) - ISNULL(NULLIF(CHARINDEX('/<', REVERSE(@String)),0) + 1,0));
Here's the result string:
<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address
All this does is lop off the last xml end tag. Is that what you wanted?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 25, 2014 at 8:05 am
Sniffer2010 (6/19/2014)
I have the following string and am trying to select only the characters before the last "</>". How can I do this?
declare @String varchar(500)set @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>'
select REVERSE(substring(REVERSE(@String),5,charindex(':',REVERSE(@String))-5))
Here is what I tried so far:
SELECT SUBSTRING(@String,CHARINDEX('</p>',@String,(CHARINDEX('</p>',@String)+1))-11,LEN(@String)-CHARINDEX('</p>',@String)-20)
SELECT RIGHT(@String,CHARINDEX('</p>',REVERSE(@String),0)-1)
select LEN(@String)
select charindex('Reviewed By: ',(@String))
select charindex('Reviewed By: ',Reverse(@String)) + datalength('</p>')+1,len(@String)
select SUBSTRING(@String, 61,10)
How can I do this When I run your above query?
home Adress here
This?
declare @String varchar(500)
set @String = '<p>Assessed By: Michael Jordan Yagnesh</p>
<p>Reviewed By: Fred Smith</p>
<p>Home Address</p>'
select REVERSE(substring(REVERSE(@String),5,charindex('<',REVERSE(@String), len(@STRING))))
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply