Split hyphenated nvarchar value

Question

I have a table with 6 columns one of which is a NVARCHAR (65) datatype.

All of the entries in this table have the format XXXXX-nnnnnn.

I want to be able to use the '-' as a delimiter and construct a query that pulls out the value after the '-' into a temporary table so I can run another query against a column containing just the nnnnnnn values.

I believe PATINDEX needs to be in there somewhere but it all seems hard and difficult.

score 3 · Answer 1 · answered Jul 31 '17 at 21:41

In this spirit of showing a man how to catch a fish, I'll provide a few possibilities that should point you in the right direction. First, we need to have a test table, with some test data, so we can try some things:

We'll do this in tempdb, so we don't affect anything important:

USE tempdb;

Here, we'll create the table:

IF OBJECT_ID(N'dbo.Split', N'U') IS NOT NULL
DROP TABLE dbo.Split;
CREATE TABLE dbo.Split
(
    SomeCol varchar(65) NOT NULL
);

And insert 10 rows of data:

INSERT INTO dbo.Split (SomeCol)
SELECT 'SomeData-0000000' + CONVERT(varchar(10), r.num)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))r(num);

There are several methods you might choose to "split" the SomeCol column data. If the portion you want to split off is always a consistent number of digits, you could use the RIGHT function. If the portion you need to split away from the right-hand-side is variable length, you could use SUBSTRING and CHARINDEX.

RIGHT returns the right part of a character string with the specified number of characters.

SUBSTRING returns part of a character, binary, text, or image expression. CHARINDEX searches an expression for another expression and returns its starting position if found. Used together, SUBSTRING and CHARINDEX can slice-and-dice strings in numerous ways.

SELECT *
    , [RIGHT] = RIGHT(SomeCol, 6)
    , [SUBSTRING/CHARINDEX] = SUBSTRING(SomeCol, CHARINDEX('-', SomeCol) + 1, LEN(SomeCol))
FROM dbo.Split

The results:

╔═══════════════════╦════════╦═════════════════════╗
║ SomeCol           ║ RIGHT  ║ SUBSTRING/CHARINDEX ║
╠═══════════════════╬════════╬═════════════════════╣
║ SomeData-00000000 ║ 000000 ║ 00000000            ║
║ SomeData-00000001 ║ 000001 ║ 00000001            ║
║ SomeData-00000002 ║ 000002 ║ 00000002            ║
║ SomeData-00000003 ║ 000003 ║ 00000003            ║
║ SomeData-00000004 ║ 000004 ║ 00000004            ║
║ SomeData-00000005 ║ 000005 ║ 00000005            ║
║ SomeData-00000006 ║ 000006 ║ 00000006            ║
║ SomeData-00000007 ║ 000007 ║ 00000007            ║
║ SomeData-00000008 ║ 000008 ║ 00000008            ║
║ SomeData-00000009 ║ 000009 ║ 00000009            ║
╚═══════════════════╩════════╩═════════════════════╝

If you are using SQL Server 2016 or newer, you could use the STRING_SPLIT functionality to automatically split the contents of the column into multiple columns:

SELECT *
FROM dbo.Split
CROSS APPLY string_split(SomeCol, '-') ss;

Split hyphenated nvarchar value

1 Answers1