Extract data between characters SQL

Question

I am trying to retrieve random data between characters

Data Example:

ABC-33-Ha8o89-00
ABC-232-Ui7380-000

Out of the above example, I am trying to get Ha8o89 and Ui7380. Basically any data after two dashes from the left and one dash from the right.

What have you tried? Will there _always_ be exactly three dashes? What do you want the result to be if there are no dashes, one dash, two dashes, or four+? — Aaron Bertrand, Jan 06 '22 at 16:40
@AaronBertrand I have tried the substring approach in which I tried to define starting at how many spaces from left I want to extract the values from for x amount of characters select SUBSTRING(column, 8,6) as 'Field' from table That's why I was seeking help for the dash approach. The problem with this approach is, the amount of characters before the extraction or after extraction could vary which is whs, it is always 3 dashes. 2 before and one after — 852post, Jan 06 '22 at 16:45
You clearly can't use number of spaces / characters because the components aren't fixed size. Have you looked at `CHARINDEX`? Does the table have a key? What are the answers to my other questions (how many dashes)? What version of SQL Server are you using? — Aaron Bertrand, Jan 06 '22 at 16:49
And can a `.` appear _in the data_? Can the 3rd element ever be more than 128 characters? — Aaron Bertrand, Jan 06 '22 at 16:50
@AaronBertrand It is always 3 dashes, two before and one after. No other special characters. The maximum amount of characters to be extracted is around 15. I believe it is version 2017 of sql server. I am not very familiar with charindex — 852post, Jan 06 '22 at 16:58
I know exactly what direction you're going in, @AaronBertrand . ;) — Thom A, Jan 06 '22 at 17:01
Does this answer your question? [Using T-SQL, return nth delimited element from a string](https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string) — Thom A, Jan 06 '22 at 17:01

score 1 · Answer 1 · answered Jan 06 '22 at 17:03

Given this data:

CREATE TABLE dbo.RandomData(StringValue varchar(128));

INSERT dbo.RandomData(StringValue) VALUES
('ABC-33-Ha8o89-00'),
('ABC-232-Ui7380-000');

A quick and dirty way, given that you state that there are always three dashes:

SELECT StringValue,
       Parsed = PARSENAME(REPLACE(StringValue,'-','.'), 2) 
       -- parsename starts right
  FROM dbo.RandomData;

On 2016+, you can use OPENJSON:

SELECT r.StringValue, Parsed = j.value
  FROM dbo.RandomData AS r
  CROSS APPLY OPENJSON ('["' 
    + REPLACE(r.StringValue, '-', '","') + '"]') AS j
  WHERE [key] = 2; -- keys are 0-based

And on any version, you can use an ordered split function (there are dozens and dozens of examples out there), here's one I grabbed from this article and this earlier answer:

CREATE FUNCTION dbo.SplitOrdered
(
    @list    nvarchar(max), 
    @delim   nvarchar(10)
)
RETURNS TABLE 
WITH SCHEMABINDING 
AS 
RETURN
(
  WITH w(n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0)) w(n)),
       k(n) AS (SELECT 0 FROM w a, w b),
       r(n) AS (SELECT 0 FROM k a, k b, k c, k d, k e, k f, k g, k h),
       p(n) AS (SELECT TOP (COALESCE(LEN(@list), 0)) 
                ROW_NUMBER() OVER (ORDER BY @@SPID) -1 FROM r),
       spots(p) AS 
       (
         SELECT n FROM p 
         WHERE (SUBSTRING(@list, n, LEN(@delim + 'x') - 1) LIKE @delim OR n = 0)
       ),
       parts(p,val) AS 
       (
         SELECT p, SUBSTRING(@list, p + LEN(@delim + 'x') - 1, 
           LEAD(p, 1, 2147483647) OVER (ORDER BY p) - p - LEN(@delim)) 
         FROM spots AS s
       )
       SELECT listpos = ROW_NUMBER() OVER (ORDER BY p), 
              Item    = LTRIM(RTRIM(val))
         FROM parts
);

Then the query is:

SELECT r.StringValue, Parsed = s.Item
  FROM dbo.RandomData AS r
  CROSS APPLY dbo.SplitOrdered(r.StringValue, '-') AS s
  WHERE s.listpos = 3; -- listpos is 1-based

All three examples in this db<>fiddle.

score 0 · Answer 2 · answered Jan 07 '22 at 12:53

You can use CHARINDEX in a CROSS APPLY (VALUES to search for the first hyphen, then feed it in again to get the next hyphen and so on. Then you simply use SUBSTRING to get the correct value.

Use NULLIF to ensure it doesn't error out if the hyphen is not found

SELECT
  *,
  ThirdValue = SUBSTRING(d.StringValue, v2.Dash2 + 1, v3.Dash3 - v2.Dash2 - 1)
FROM dbo.RandomData d
CROSS APPLY (VALUES( NULLIF(CHARINDEX('-', d.StringValue), 0) )) v1(Dash1)
CROSS APPLY (VALUES( NULLIF(CHARINDEX('-', d.StringValue, v1.Dash1 + 1), 0) )) v2(Dash2)
CROSS APPLY (VALUES( NULLIF(CHARINDEX('-', d.StringValue, v2.Dash2 + 1), 0) )) v3(Dash3);

db<>fiddle

Extract data between characters SQL

2 Answers2

Linked