CODEWITHSUNDEEP
mysqlsqlstring
Jayson
Jayson

Reputation: 83

SQL Query String Split by Delimiter

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.

Upvotes: 0

Views: 588

Answers (2)

BWS
BWS

Reputation: 3846

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

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

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)]>

Upvotes: 1

Related Questions