CODEWITHSUNDEEP
sqlsql-server
Deepak
Deepak

Reputation: 123

Substring portion of a text in SQL Server

Kindly let me know how to extract only 

IMIS Event - BITWN1802

part from the below text (leaving T1 or T1 S1).

I have tried replacing blank spaces with _ (underscore symbol) to find its charindex and so I can use the charindex position of the last _ and to substring it, but it is not working that way.

SELECT 
    CHARINDEX('_', (REPLACE([Code], ' ', '_')), 4)

enter image description here

Expected result :

enter image description here

Your help is appreciated

Upvotes: 0

Views: 106

Answers (3)

Sreenu131
Sreenu131

Reputation: 2516

Try this

DECLARE @Table AS TABLE (Data varchar(100))
INSERT INTO @Table
SELECT 'IMIS Event - BITWN1802 T2'      UNION ALL
SELECT 'IMIS Event - BITWN1802 T1'  UNION ALL
SELECT 'IMIS Event - BITWN1802 T1 S1'

SELECT Data,
      CONCAT(SUBSTRING(Data,0,CHARINDEX('-',Data)-1),' - ',SUBSTRING(ReqData,0,CHARINDEX(' ',ReqData))) As ReqData
FROM
(
SELECT Data, LTRIM(SUBSTRING(Data,CHARINDEX('-',Data)+1,LEN(Data))) As ReqData
FROM @Table
)dt

Result

    Data                            ReqData
-------------------------------------------------------
IMIS Event - BITWN1802 T2       IMIS Event - BITWN1802
IMIS Event - BITWN1802 T1       IMIS Event - BITWN1802
IMIS Event - BITWN1802 T1 S1    IMIS Event - BITWN1802

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please try like this-

CREATE TABLE sts
(
    Vals VARCHAR(200)
)
GO

INSERT INTO sts VALUES
 ('IMIS Event - BITWN1802 ')
,('IMIS Event - BITWN1802 T1 T2')
,('IMIS Event - BITWN18023 T1 T2')
,('IMIS Event - BITWN1801 T1 T2')
,('IMIS Event - BITWN1802 X')
,('')
,(NULL)
GO

SELECT IIF(a<>0,SUBSTRING(Vals,CHARINDEX('IMIS Event - BITWN', Vals , 0),CHARINDEX(' ', Vals , a)),Vals) Vals
FROM
(
    SELECT 
        *,
        IIF(CHARINDEX('IMIS Event - BITWN', Vals , 0)>0, CHARINDEX('IMIS Event - BITWN', Vals , 0) + LEN('IMIS Event - BITWN')
        ,0) a
    FROM sts
)x

OUTPUT

Vals
------------------------------
IMIS Event - BITWN1802 
IMIS Event - BITWN1802 
IMIS Event - BITWN18023 
IMIS Event - BITWN1801 
IMIS Event - BITWN1802 

NULL

(7 rows affected)

Upvotes: 1

Arulkumar
Arulkumar

Reputation: 13237

If you want the fixed length of characters from the beginning, you can use the LEFT, so the query will be:

SELECT LEFT([Code], 22) AS [Code]

Upvotes: 2

Related Questions