CODEWITHSUNDEEP
sqlsql-server
Nani3
Nani3

Reputation: 487

Using CHARINDEX IN SQL SERVER TO SPLIT A STRING WITH MULTIPLE SPECIAL CHARACTERS

I have a field called contract details with the following contents

I would like to split it as below

Money | rate | days | year

25030.1 | 12 | 30 | 360

How can Archive this using CHARINDEX AND SUBSTRING

Sometimes the contains appear like below, with no days but the days column can be recalculated.

YEAR~360!RATE~16!Money~421777.78!

year | rate | money

360 | 12 | 25030.1

Upvotes: 0

Views: 1320

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

Here is one approach:

SELECT
    SUBSTRING(col, CHARINDEX('Money', col) + 6,
                   CHARINDEX('!RATE~', col) - CHARINDEX('Money', col) - 6) AS Money,
    SUBSTRING(col, CHARINDEX('!RATE~', col) + 6,
                   CHARINDEX('!DAYS~', col) - CHARINDEX('!RATE~', col) - 6) AS rate,
    SUBSTRING(col, CHARINDEX('!DAYS~', col) + 6,
                   CHARINDEX('!YEAR~', col) - CHARINDEX('!DAYS~', col) - 6) AS days,
    LEFT(SUBSTRING(col, CHARINDEX('!YEAR~', col) + 6, LEN(col)),
    SUBSTRING(col, CHARINDEX('!YEAR~', col) + 6,
                   CHARINDEX('!', SUBSTRING(col, CHARINDEX('!YEAR~', col) + 6, LEN(col))) - 1) AS year
FROM yourTable;

screen capture from demo link below

Demo

Note that the above query, as the demo shows, is robust with regard to your target text appearing anywhere inside a larger string.

Upvotes: 2

Related Questions