CODEWITHSUNDEEP
sql
Jason
Jason

Reputation: 79

Extracting substring from Column A and inserting it into Column B

The values in Column A are formatted like the sample value below. I need to grab the value after the first dash (no space) and before the second dash (space on both sides). If the target value can be captured simply by the spacing difference that will be fine. Alternatively, the "CC" in the sample can have different values, but it is always 2 characters, so the beginning can also be identified as colon, space, 2 characters, dash (with no space).

Sample Value:

John Doe: CC- Grab This Value - Some Other Random String - 01/16/2018

So in this example column B should be populated with Grab This Value

Upvotes: 1

Views: 359

Answers (2)

Rajat
Rajat

Reputation: 5793

If this is SQL Server, you could use cross apply. I would include the space as part of the charindex just to be extra cautious, plus it's going to to simply the solution a little bit. The way this works is- you capture everything before the occurrence of - (with spaces around) and then capture everything after the first occurrence of - in the substring you just captured.

select col_a, col_b
from your_table
cross apply (select left(col_a, charindex(' - ', col_a)-1) as txt) t2
cross apply (select right(txt, charindex('-', reverse(txt))-2) as col_b) t3;

DEMO

Upvotes: 1

CR7SMS
CR7SMS

Reputation: 2584

In SQL Server, you could use something like:

DECLARE @text varchar(100)='John Doe: CC-Grab This Value - Some Other Random String - 01/16/2018'

select @text as ColumnA,
       SUBSTRING(@text,
                 CHARINDEX('-',@text,1)+1,
                 CHARINDEX('-',@text,CHARINDEX('-',@text,1)+1)-CHARINDEX('-',@text,1)-1) as ColumnB

Upvotes: 0

Related Questions