SQL Query String Split by Delimiter

Question

I have a query that returns a field that is stored like this year-month-info. So an example of data would be 2018-February-Full Page. I need to split this by the '-' value so instead of just the one column returned, I would get three columns.

Year Month Text
2018 February Full Page

The text is not fixed formatting to the catch would have to be by the '-' symbol and the data will always be split this way. How to do this easily?

EDIT:

Here is my code

Declare @Str varchar(80)

Select @Str = IPDesc from vw_MRA_AdContracts

Declare @first_dash int = CharIndex('-', @Str, 1) Declare @last_dash int = CharIndex('-', Reverse(LTrim(Rtrim(@Str))))

Select profileid, OrgName, @Str,

Substring(@Str, 1, @first_dash-1) as AdYear, 
Substring(@Str, @first_dash+1, Len(@Str)-@first_dash-@last_dash) as AdMonth, 
Substring(@Str, @last_dash+@first_dash, Len(@Str)) as AdSold,

from vw_MRA_AdContracts

The problem is that it looks like the variable isn't doing a loop through all records available and grabbing one and then splitting the one record. So for AdYear, AdMonth and AdSold, I am getting the exact same value for each returning record even if it doesn't match what that record has.

Possible duplicate of [How to split strings in SQL Server](https://stackoverflow.com/questions/6619810/how-to-split-strings-in-sql-server) — Matt Spinks, Jan 04 '18 at 19:41
Check out: https://coderwall.com/p/zzgo-w/splitting-strings-with-mysql — clinomaniac, Jan 04 '18 at 19:43

score 1 · Answer 1 · answered Jan 04 '18 at 20:24

A other way is to use this Query. The CONCAT in the Query is to prevent wrong result if the STRING has less than 3 Arguments and for the first argument (Year) is the first SUBSTRING_INDEX not necessary.

You only must change the String with your field from your Table.

SELECT
    SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan-Hello",'--'), '-', 1),'-',-1) AS 'Year',
    SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan-Hello",'--'), '-', 2),'-',-1) AS 'Month',
    SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan-Hello",'--'), '-', 3),'-',-1) AS 'Text';

Sample

MariaDB [(none)]> SELECT
    ->     SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan-Hello",'--'), '-', 1),'-',-1) AS 'Year',
    ->     SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan-Hello",'--'), '-', 2),'-',-1) AS 'Month',
    ->     SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan-Hello",'--'), '-', 3),'-',-1) AS 'Text';
+------+-------+-------+
| Year | Month | Text  |
+------+-------+-------+
| 2018 | Jan   | Hello |
+------+-------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SELECT
    ->     SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan",'--'), '-', 1),'-',-1) AS 'Year',
    ->     SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan",'--'), '-', 2),'-',-1) AS 'Month',
    ->     SUBSTRING_INDEX( SUBSTRING_INDEX( CONCAT("2018-Jan",'--'), '-', 3),'-',-1) AS 'Text';
+------+-------+------+
| Year | Month | Text |
+------+-------+------+
| 2018 | Jan   |      |
+------+-------+------+
1 row in set (0.00 sec)

MariaDB [(none)]>

Using this, I am getting the error CONCAT is not a recognized built-in function name. — Jayson, Jan 04 '18 at 21:24

BWS · Answer 2 · 2018-01-04T22:44:19.400

0

you could try something like this:

declare @STR varchar(80) = 'WHATEVER-YOUR-STRING IS'

For example: '2018-February-Full Page'

declare @fist_dash int = CHARINDEX('-',@STR,1)
declare @last_dash int = CHARINDEX('-',REVERSE(LTRIM(RTRIM(@STR))))

select @STR
,[YEAR]=substring(@STR,1,@fist_dash-1)
,[MONTH]=substring(@STR,@fist_dash+1,LEN(@STR)-@fist_dash-@last_dash)
,[TEXT]=substring(@STR,@fist_dash+1+LEN(substring(@STR,@fist_dash+1,LEN(@STR)-@fist_dash-@last_dash+1)),LEN(@STR))

Returns this:

(No column name)        YEAR     MONTH      TEXT
2018-February-Full Page 2018     February   Full Page
WHATEVER-YOUR-STRING IS WHATEVER YOUR       STRING IS

edited Jan 04 '18 at 22:44

answered Jan 04 '18 at 19:54

BWS

3,786
18
25

So, this looks good but the string isn't always 2018-February-Full Page, it can be anything. I would need to load the field from the table and then split the data that is in it. The data will always be %-%-% for format though. – Jayson Jan 04 '18 at 21:16
Check my edit ... not knowing what your actual data is, or where it comes from, this "example" looks like it works for you. You may have to do a little work to get it to work in your database. – BWS Jan 04 '18 at 22:39
I have updated my original post with my code and an edited response. – Jayson Jan 08 '18 at 16:40

SQL Query String Split by Delimiter

2 Answers2