asked on

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*******************
Column1
28khteiwyhhgjstkeyiyg TicketId=9087764 thiwyAohigyi3jshotgh
ththeshigheowihght TicketId= 8976548 whtiohoihgahtoiehquibx
hgstjteiti TicketId= 9084778 wehowereihihanfdkhehghie
Column2
<testing><testing1>server is down. </testing1></testing>
<testing><testing1>wrongIP </testing1></testing>
<testing><testing1>NoerrorsFound</testing1></testing>
Column3
<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
9087764
8976548
9084778

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

ste5an

E.g.

DECLARE @Sample TABLE ( Column1 NVARCHAR(MAX) );

INSERT  INTO @Sample
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

Harman15

ASKER

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)

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Brian Crowe