January 14, 2009 at 5:22 am
I need to select only a part of a text string....
I have the following 3 rows...
Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId
Provider PraNum="3804917" Funder DisplayName="Second" emMA="28223" Industry RefId0901062356255634nv4nkt RefId
Provider PraNum="3802078" Funder DisplayName="BEST" MemMA="41807" Industry RefId 090107002846839na1e62m RefId
I need to SELECT everything between the two words 'Funder' & 'Industry'
IE my results must be:
DisplayName="Health" MemMAPlan="41807"/> </
DisplayName="Second" MemMA="28223"/> </
DisplayName="BEST" MemMA="41807"/> </
Any help will be greatly appreciated...
Thanks
January 14, 2009 at 5:31 am
You can make use of String function CHARINDEX along with SUBSTRING
-Vikas Bindra
January 14, 2009 at 5:35 am
This is what I did but it returns more than what I need...
SELECT SUBSTRING(routingheader, charindex('funder',routingheader), charindex('funder',routingheader)) AS ROUTINGHEADER
January 14, 2009 at 5:39 am
This is what I used but it returns everything AFTER the word Industry as well..
SELECT SUBSTRING(routingheader, charindex('funder',routingheader), charindex('industry',routingheader)) AS ROUTINGHEADER
All I need is what is between the two words: funder and industry
January 14, 2009 at 6:11 am
Just a little modification in the query....
Note:
This code assumes that there exists values "Funder" & "Industry" in the column. Otherwise, you have to manually handle those exceptions by filtering it out.
SELECTCHARINDEX( 'Funder', routingheader ),
CHARINDEX( 'Industry', routingheader ),
SUBSTRING( routingheader, CHARINDEX( 'Funder', routingheader ) + LEN( 'Funder' ),
CHARINDEX( 'Industry', routingheader ) - CHARINDEX( 'Funder', routingheader )
- LEN( 'Funder' ) ) AS ROUTINGHEADER
FROM
(
SELECT 'Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId' AS routingheader
UNION ALL
SELECT 'Provider PraNum="3804917" Funder DisplayName="Second" emMA="28223" Industry RefId0901062356255634nv4nkt RefId' AS routingheader
UNION ALL
SELECT 'Provider PraNum="3802078" Funder DisplayName="BEST" MemMA="41807" Industry RefId 090107002846839na1e62m RefId' AS routingheader
) R
--Ramesh
January 14, 2009 at 6:20 am
Terence Keys (1/14/2009)
This is what I used but it returns everything AFTER the word Industry as well..SELECT SUBSTRING(routingheader, charindex('funder',routingheader), charindex('industry',routingheader)) AS ROUTINGHEADER
All I need is what is between the two words: funder and industry
Good start, Terence, but it's a little more complex than that. Run the following piece of code and you will see why.
DECLARE @routingheader VARCHAR(200)
SET @routingheader = 'Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId'
SELECT @routingheader,
MatchValue = SUBSTRING(@routingheader, 34, 38),
Startpos = charindex('funder',@routingheader)+LEN('funder ')+1,
Length = charindex('industry',@routingheader) - (charindex('funder',@routingheader)+LEN('funder ')+2),
SUBSTRING(@routingheader, charindex('funder',@routingheader)+LEN('funder ')+1,
charindex('industry',@routingheader) - (charindex('funder',@routingheader)+LEN('funder ')+2))
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 14, 2009 at 9:22 pm
Thanks for the help!
January 15, 2009 at 7:22 am
SELECT CHARINDEX( 'Funder', routingheader ),
CHARINDEX( 'Industry', routingheader ),
SUBSTRING( routingheader, CHARINDEX( 'Funder', routingheader ) + LEN( 'Funder' ),
CHARINDEX( 'Industry', routingheader ) - CHARINDEX( 'Funder', routingheader )
- LEN( 'Funder' ) ) AS ROUTINGHEADER
FROM
(
SELECT 'Provider PraNum="3802078" Funder DisplayName="Health" MemMAPlan="41807" Industry RefId 0901070749026822a6f28a RefId' AS routingheader
UNION ALL
SELECT 'Provider PraNum="3804917" Funder DisplayName="Second" emMA="28223" Industry RefId0901062356255634nv4nkt RefId' AS routingheader
UNION ALL
SELECT 'Provider PraNum="3802078" Funder DisplayName="BEST" MemMA="41807" Industry RefId 090107002846839na1e62m RefId' AS routingheader
) R
A little modification in Ramesh's code...
SELECT CHARINDEX( 'Funder', routingheader ),
CHARINDEX( 'Industry', routingheader ),
SUBSTRING( routingheader, CHARINDEX( 'DisplayName', routingheader)
CHARINDEX( 'Industry', routingheader ) - CHARINDEX( 'Funder', routingheader )
- LEN( 'Funder' ) ) AS ROUTINGHEADER
karthik
January 15, 2009 at 8:29 am
Terence Keys (1/14/2009)
Thanks for the help!
So, what did your final code end up looking like?
--Jeff Moden
January 15, 2009 at 11:11 pm
Thanks for all the help!! you pointed
me in the right direction with the substring and charindex..
The following code worked for me...
DECLARE eRA_Report CURSOR FOR
SELECT originalmessageid
FROM AM
WHERE Messagetype = 'Normal/Remittanceadv' AND RecipientAddress LIKE '%PRO027%' AND Timestamp BETWEEN @StartDate AND @EndDate
--and originalmessageid in ('090112123227693mec1xtk')
OPEN eRA_Report
FETCH NEXT FROM eRA_Report INTO @originalmessageid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @substring_input_2 = (SELECT CHARINDEX('DisplayName="', routingheader) from am where originalmessageid = @originalmessageid)
SET @substring_input_3 = (select CHARINDEX(' ', routingheader) from am where originalmessageid = @originalmessageid)
SET @substring_input_3 = @substring_input_3 - @substring_input_2 + 9
SET @substring_input_2 = @substring_input_2 - 9
SET @routingheader = (select substring(routingheader,@substring_input_2,@substring_input_3) from am where originalmessageid = @originalmessageid)
SET @substring_input_3 = null
SET @substring_input_2 = null
INSERT INTO eRA_TEMP_Table (ORIGINALMESSAGEID, REFERENCEMESSAGEID, TIMESTAMP, MESSAGETYPE, SENDER, RECIPIENTADDRESS, OBO, QUANTITY, TRANSACTIONNUMBER, PRIORITY, COMPRESSION, SECURITY,RoutingHeader)
SELECT ORIGINALMESSAGEID, REFERENCEMESSAGEID, TIMESTAMP, MESSAGETYPE, SENDER, RECIPIENTADDRESS, OBO, QUANTITY, TRANSACTIONNUMBER, PRIORITY, COMPRESSION, SECURITY, @RoutingHeader as 'RoutingHeader'
FROM AM
WHERE originalmessageid = @originalmessageid
SET @routingheader = null
FETCH NEXT FROM eRA_Report INTO @originalmessageid
END
January 15, 2009 at 11:22 pm
Oh dear... thanks for sharing, but why on Earth did you use a cursor? That's gonna slow it down, alot!
--Jeff Moden
January 16, 2009 at 1:06 am
It does run longer yes.. but it's a very small db and the report only runs once a week..
Thanks again for the help!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply