Extract data between characters SQL-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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