CODEWITHSUNDEEP
sqlsql-server
Dev
Dev

Reputation: 367

Extract Certain String after Some Expression

I have table customer_info in which a column PrintData which contains many information. I would like to get Transaction id from that column.

The column data look like this:

    <Line28>.TVR: 4000008000</Line28>
    <Line29>.IAD: 06020103649D00</Line29>
    <Line30>.TSI: E800</Line30>
    <Line31>.ARC: 00</Line31>
    <Line32>.CVM: PIN VERIFIED</Line32>
    <Line33>.TRAN ID: 000000000075169</Line33>

I would like to get only 000000000075169 i.e. TRAN ID:

I have tried this as:

SUBSTRING(PrintData,CHARINDEX('TRAN ID: ',PrintData),CHARINDEX('</Li',PrintData))

but it is not giving write answer.

Upvotes: 1

Views: 59

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I would simply use APPLY :

select @PrintData AS Original_string, 
       substring(tran_id, 1, charindex('</', tran_id) -1) as Tran_ID
from ( values ( substring(PrintData, charindex('TRAN ID:', PrintData) + 8, len(PrintData)) )
     ) t(tran_id);

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

Can you please with the following query. 

DECLARE @PrintData AS VARCHAR (200) = '<Line33>.TRAN ID: 000000000075169</Line33>';

SELECT SUBSTRING(@PrintData, 
                 CHARINDEX('TRAN ID: ', @PrintData) + LEN('TRAN ID: '), 
                 CHARINDEX('</Li',@PrintData) - (CHARINDEX('TRAN ID: ', @PrintData) + LEN('TRAN ID: '))
                 );

The syntax is SUBSTRING (expression, start_position, length)

UPDATE:

As per the comment by MarcinJ, for the multiple instance of </Line, the folllowing query will work.

DECLARE @PrintData VARCHAR(2000) = '
    <Line28>.TVR: 4000008000</Line28>
    <Line29>.IAD: 06020103649D00</Line29>
    <Line30>.TSI: E800</Line30>
    <Line31>.ARC: 00</Line31>
    <Line32>.CVM: PIN VERIFIED</Line32>
    <Line33>.TRAN ID: 000000000075169</Line33>
    ';

DECLARE @FindString AS VARCHAR (20) = 'TRAN ID: ';
DECLARE @LenFindString AS INT = LEN(@FindString);

SELECT SUBSTRING(@PrintData, 
                 CHARINDEX(@FindString, @PrintData) + @LenFindString, 
                 CHARINDEX('</Line', @PrintData, CHARINDEX(@FindString, @PrintData)) - (CHARINDEX(@FindString, @PrintData) + @LenFindString)
                 );

Upvotes: 1

MarcinJ
MarcinJ

Reputation: 3639

DECLARE @z NVARCHAR(MAX) = '
    <Line28>.TVR: 4000008000</Line28>
    <Line29>.IAD: 06020103649D00</Line29>
    <Line30>.TSI: E800</Line30>
    <Line31>.ARC: 00</Line31>
    <Line32>.CVM: PIN VERIFIED</Line32>
    <Line33>.TRAN ID: 000000000075169</Line33>
    '

    SELECT SUBSTRING(@z, CHARINDEX('TRAN ID: ', @z) + 9 -- offset charindex by 9 characters to omit the 'TRAN ID: '
                       , CHARINDEX('</Li', @z, CHARINDEX('TRAN ID: ', @z))-CHARINDEX('TRAN ID: ', @z) - 9) -- find the </Li AFTER the occurence of TRAN ID, and subract 9 to account for the offset

Yields 000000000075169.

Upvotes: 1

Related Questions