SQL server: TSQL to pull Particular value from a sting

Hello Experts,
               I need to pull a particular value from selected columns of a table. My table looks like this (attached). I need to pull ticketID from column 1 and its location could be anywhere in the string. And from column 2 and 3, i need to pull certain strings and combine them into a  result set. Attached snippet shows current results and needed results. Please suggest.

In case attachment is not working, please check below.
**************Current Structure*******************
28khteiwyhhgjstkeyiyg TicketId=9087764 thiwyAohigyi3jshotgh
ththeshigheowihght TicketId= 8976548 whtiohoihgahtoiehquibx
hgstjteiti TicketId= 9084778 wehowereihihanfdkhehghie
<testing><testing1>server is down. </testing1></testing>
<testing><testing1>wrongIP </testing1></testing>
<Parameters><Parameters1>Need intervention </Parameters></Parameters1>
<Parameters><Parameters1>Contact Network team</Parameters></Parameters1>
<Parameters><Parameters1>No action needed</Parameters></Parameters1>

************Needed Result Set:************************
2 colums (TicketID and description)
TicketId ---to pull from column1

Description -- to pull from column 2 and 3 and add them
server is down. Need intervention
wrongIP Contact Network team
NoerrorsFound No action needed
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:


VALUES  ( N'28khteiwyhhgjstkeyiyg TicketId=9087764 thiwyAohigyi3jshotgh' ),
        ( N'ththeshigheowihght TicketId= 8976548 whtiohoihgahtoiehquibx' ),
        ( N'hgstjteiti TicketId= 9084778 wehowereihihanfdkhehghie' );

WITH    Data
          AS ( SELECT   REPLACE(S.Column1, '= ', '=') AS Patched
               FROM     @Sample S
    SELECT  SUBSTRING(D.Patched, PATINDEX('%TicketId=%', D.Patched) + 9, CHARINDEX(' ', SUBSTRING(D.Patched, PATINDEX('%TicketId=%', D.Patched) + 9, 1024)) - 1)
    FROM    Data D;

Open in new window

Harman15Author Commented:
Hi ste5an, Thanks for your advice. The column1 in reality has more than 100 million rows. Instead of
VALUES  ( N'28khteiwyhhgjstkeyiyg TicketId=9087764 thiwyAohigyi3jshotgh' ),
        ( N'ththeshigheowihght TicketId= 8976548 whtiohoihgahtoiehquibx' ),
        ( N'hgstjteiti TicketId= 9084778 wehowereihihanfdkhehghie' );

How can i make it like
INSERT  INTO @Sample Values
 (select column1 from DBname.Tablename)
ste5anSenior DeveloperCommented:
Why would you do that?

It's a sample. Just replace @Sample with your table name.
Brian CroweDatabase AdministratorCommented:
Assuming your XML is well-formed and BTW your example's aren't as you have the closing braces reversed in your sample data.

	Column1		VARCHAR(500),
	Column2		VARCHAR(500),
	Column3		VARCHAR(500)

INSERT @Test (Column1, Column2, Column3)
VALUES ('28khteiwyhhgjstkeyiyg TicketId=9087764 thiwyAohigyi3jshotgh', '<testing><testing1>server is down. </testing1></testing>', '<Parameters><Parameters1>Need intervention </Parameters1></Parameters>'),
	('ththeshigheowihght TicketId= 8976548 whtiohoihgahtoiehquibx', '<testing><testing1>wrongIP </testing1></testing>', '<Parameters><Parameters1>Contact Network team</Parameters1></Parameters>'),
	('hgstjteiti TicketId= 9084778 wehowereihihanfdkhehghie', '<testing><testing1>NoerrorsFound</testing1></testing>', '<Parameters><Parameters1>No action needed</Parameters1></Parameters>');

SELECT RTRIM(SUBSTRING(LTRIM(SUBSTRING(Column1, CHARINDEX('TicketId=', Column1, 1) + 9, LEN(Column1))), 1,
		CHARINDEX(' ', LTRIM(SUBSTRING(Column1, CHARINDEX('TicketId=', Column1, 1) + 9, LEN(Column1))), 1))) AS TicketId,
	CAST(CAST(Column2 AS XML).query('data(/testing/testing1)') AS VARCHAR(MAX)) AS Column2Value,
	CAST(CAST(Column3 AS XML).query('data(/Parameters/Parameters1)') AS VARCHAR(MAX)) AS Column3Value
FROM @Test

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Harman15Author Commented:
Thanks a lot Brian and ste5an, your suggestions is giving me the expected results.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.