1

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

urdearboy
  • 14,439
  • 5
  • 28
  • 58
Jason
  • 79
  • 6
  • 4
    Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product and string functions are highly vendor specific. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) –  Feb 24 '20 at 22:06
  • https://stackoverflow.com/a/37160899/6706419 – urdearboy Feb 24 '20 at 22:14

2 Answers2

1

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

Radagast
  • 5,102
  • 3
  • 12
  • 27
0

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
CR7SMS
  • 2,520
  • 1
  • 5
  • 13