Extract string before and after from string pattern in SQL Sever

Question

I want to extract AC No, AC Name and IFS Code from given text. I am able to extract using below code but text positon might change and its not working for me.

For example:

UserInfo="AC No: 644640011906AC Name: AkhilAC Type: CurrentIFSC Code: SBIB0003242"

or

UserInfo="IFSC Code: SBIB0003242AC No: 644640011906AC Name: AkhilAC Type: Current"

SQL:

SELECT LTRIM(RTRIM(REPLACE(RIGHT(UserInfo,charindex(':',reverse(UserInfo))-1),'IFSC Code:','')))

Request someone to help with the SQL queries.

Don't store such data in the first place. SQL, the language, is a query language, not a processing language. It's very weak in string processing. Storing multiple values in a single field violates the most basic design rule too, the 1st Normal Form. Process the data before loading it and insert each field in a separate column. That's the only way you can use indexes to accelerate queries instead of scanning and parsing the entire table on every query execution — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 27, 2020 at 7:32
You could use a really complex regex but T-SQL doesn't support reqular expressions. You could try splitting by the space character and then by : with SPLIT_STRING but that would produce rows of values, not pairs. If the data was stored in XML or JSON form you could use T-SQL's XML or JSON functions to parse the data but again, you'd end up scanning entire tables each time. And XML indexes are not as efficient as separate indexed columns — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 27, 2020 at 7:36
In SQL Server 2016 and later you could use R or Python scripts in a query to parse the data but again, why not do that while loading and store the data in a proper relational schema? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 27, 2020 at 7:36
SQL version is 2012 and there in no space betweeen Current and IFSC — Imran Azam
– Imran Azam, Commented Aug 27, 2020 at 7:39
I have just copied data from Excel file and changed the value. So below string i am getting from Excel UserInfo="AC No: 644640011906AC Name: AkhilAC Type: CurrentIFSC Code: SBIB0003242" — Imran Azam
– Imran Azam, Commented Aug 27, 2020 at 7:43

iamdave · Accepted Answer · 2020-08-27 08:32:48Z

Welcome to the wonderful world of SQL Server string manipulation!

As the other commenters have said, importing data like this is a terrible idea. If you can, focus your efforts on getting a better, more structured data source.

That said, if you absolutely can't change your data source and don't want to resort to CLR code, you are going to have to resort to some pretty ugly SQL that will only work if you are able to keep on top of the key value names you are trying to capture:

Query

declare @t table(UserID int,UserInfo varchar(200));
insert into @t values
 (1,'IFSC Code: SBIB0003242AC No: 644640011906AC Name: AkhilAC Type: Current')
,(2,'AC No: 644640011906AC Name: AkhilAC Type: CurrentIFSC Code: SBIB0003242')
;

with k as
(
    select *
    from(values('AC No:'),('AC Name:'),('AC Type:'),('IFSC Code:')) as k(k)
)
select UserID
      ,k.k
      ,ltrim(replace(substring(v.v,c.s,c.e-c.s),k.k,'')) as v
from @t as t
    cross join k
    cross apply(values(replace(
                         replace(
                           replace(
                             replace(
                               t.UserInfo
                               ,'AC No:'
                               ,case when k.k = 'AC No:' then 'AC No:' else '|' end
                               )
                             ,'AC Name:'
                             ,case when k.k = 'AC Name:' then 'AC Name:' else '|' end
                             )
                           ,'AC Type:'
                           ,case when k.k = 'AC Type:' then 'AC Type:' else '|' end
                           )
                         ,'IFSC Code:'
                         ,case when k.k = 'IFSC Code:' then 'IFSC Code:' else '|' end
                         ) + '|'
                      )
               ) as v(v)
    cross apply(values(patindex('%' + k.k + '%',v.v)
                      ,charindex('|',v.v,patindex('%' + k.k + '%',v.v))
                      )
               ) as c(s,e);

Output

+--------+------------+--------------+
| UserID |     k      |      v       |
+--------+------------+--------------+
|      1 | AC No:     | 644640011906 |
|      1 | AC Name:   | Akhil        |
|      1 | AC Type:   | Current      |
|      1 | IFSC Code: | SBIB0003242  |
|      2 | AC No:     | 644640011906 |
|      2 | AC Name:   | Akhil        |
|      2 | AC Type:   | Current      |
|      2 | IFSC Code: | SBIB0003242  |
+--------+------------+--------------+

Daniel · Accepted Answer · 2020-08-27 11:00:39Z

select  
SUBSTRING(UserInfo, CHARINDEX('AC No: ',UserInfo)+7, 
 ( select min(x*(case x when 0 then null else 1 end)) 
       from 
       ( 
         VALUES 
         (CHARINDEX('AC Name: ',UserInfo,CHARINDEX('AC No: ',UserInfo)+7)),
         (CHARINDEX('AC Type: ',UserInfo,CHARINDEX('AC No: ',UserInfo)+7)),
         (CHARINDEX('IFSC Code: ',UserInfo,CHARINDEX('AC No: ',UserInfo)+7)),
         (LEN(UserInfo)+1)
       )
       AS value(x)
       
 ) - (CHARINDEX('AC No: ',UserInfo)+7)

)
as AC_No,

SUBSTRING(UserInfo, CHARINDEX('AC Name: ',UserInfo)+9, 
 ( select min(x*(case x when 0 then null else 1 end)) 
       from 
       ( 
         VALUES 
         (CHARINDEX('AC No: ',UserInfo,CHARINDEX('AC Name: ',UserInfo)+9)),
         (CHARINDEX('AC Type: ',UserInfo,CHARINDEX('AC Name: ',UserInfo)+9)),
         (CHARINDEX('IFSC Code: ',UserInfo,CHARINDEX('AC Name: ',UserInfo)+9)),
         (LEN(UserInfo)+1)
       )
       AS value(x)
       
 ) - (CHARINDEX('AC Name: ',UserInfo)+9)
)
as AC_Name,
SUBSTRING(UserInfo, CHARINDEX('IFSC Code: ',UserInfo)+11, 

 ( select min(x*(case x when 0 then null else 1 end)) 
       from 
       ( 
         VALUES 
         (CHARINDEX('AC No: ',UserInfo,CHARINDEX('IFSC Code: ',UserInfo)+11)),
         (CHARINDEX('AC Type: ',UserInfo,CHARINDEX('IFSC Code: ',UserInfo)+11)),
         (CHARINDEX('AC Name: ',UserInfo,CHARINDEX('IFSC Code: ',UserInfo)+11)),
         (LEN(UserInfo)+1)
       )
       AS value(x)
       
 ) - (CHARINDEX('IFSC Code: ',UserInfo)+11)

)
as IFSC_Code

from test

Then it is probably best to do this once, while you load the data into a staging table etc. and from there store these values in appropriate fields in the production table.

Fiddle Example: https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=a5fdfbbd6a0b4657602af39744993a91

Collectives™ on Stack Overflow

Extract string before and after from string pattern in SQL Sever

2 Answers 2

Query

Output

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Query

Output

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related