How to split string into different colunms SQL query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

This is the table
CounterKey
CIVUS0.35V
916148-010-CIVUS
916425-010-CIVUS
CIVUS0.35B
257198-010-CIVUS
916425-010-CIVUS
UCIVUS0.35P
7116-7004-08-UCIVUS
916148-010-UCIVUS
UCIVUS0.35P

I want to separate the counterKey into different new different colunms e.g

SQL

CounterKey             FirstPart       SecondPart        LastPart
CIVUS0.35V               CIVUS0          35                V
916148-010-CIVUS         916148          010               CIVUS
UCIVUS0.35P              UCIVUS0         35                P
7116-7004-08-UCIVUS      7116-700408     08                UCIVUS

What I have tried:

I tried this code:

SQL

SELECT top 10
     CounterKey,
	    REVERSE(PARSENAME(REPLACE(REVERSE(CounterKey), ',', '.'), 1)) AS FirstPart,
        REVERSE(PARSENAME(REPLACE(REVERSE(CounterKey), '',''), 2)) AS Lastpart    
  FROM [HHMachine].[Komax].[WPCS_Counter]

It displays data like this:

SQL

CounterKey	               FirstPart	Lastpart
CIVUS0.35V	                  CIVUS0	35V
916148-010-CIVUS	916148-010-CIVUS	NULL
916425-010-CIVUS	916425-010-CIVUS	NULL
CIVUS0.35B	                    CIVUS0	35B
257198-010-CIVUS	257198-010-CIVUS	NULL
916425-010-CIVUS	916425-010-CIVUS	NULL
UCIVUS0.35P	                 UCIVUS0	35P
7116-7004-08-UCIVUS	7116-7004-08-UCIVUS	NULL
916148-010-UCIVUS	916148-010-UCIVUS	NULL
UCIVUS0.35P	                 UCIVUS0	35P

Posted 21-Sep-20 0:38am

Sincobile Nhlengethwa

Updated 22-Sep-20 2:49am

Add a Solution

2 solutions

Solution 1

To be honest, SQL string handling is at best poor and cumbersome - if you do fix up your SQL to do this then the first time it changes or you find a "new format" key in there (and you will, you will) you are going to be looking at a real PITA to get it working again.

Instead of breaking it up in SQL, break it up in your presentation language (where the string handling is much, much easier and more maintainable) and store the "broken up" bits in separate SQL columns - you can then use an SQL Calculated Column to recombine them very easily for when you need the whole key value.

It's a much easier system to maintain and to work with!

Posted 21-Sep-20 1:04am

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-09-22T02:49:00

You were pretty close to find solution...

SQL

;WITH CTE AS
(
  SELECT CounterKey,
    PARSENAME(CounterKey, 2) AS Part1,
    LEFT(PARSENAME(CounterKey, 1), 2) AS Part2,
    RIGHT(PARSENAME(CounterKey, 1), 1) AS Part3
  FROM SomeData
  WHERE CHARINDEX('.', CounterKey)>0
  UNION ALL
  SELECT  CounterKey,
    PARSENAME(REPLACE(CounterKey, '-', '.'), 3) AS Part1,
    PARSENAME(REPLACE(CounterKey, '-', '.'), 2) AS Part2,
    PARSENAME(REPLACE(CounterKey, '-', '.'), 1) AS Part3
  FROM SomeData
  WHERE CHARINDEX('-', CounterKey)>0
)
SELECT *
FROM CTE

db<>fiddle[^]

Result:

CounterKey 	Part1 	Part2 	Part3
CIVUS0.35V 	CIVUS0 	35 	V
CIVUS0.35B 	CIVUS0 	35 	B
UCIVUS0.35P 	UCIVUS0 	35 	P
UCIVUS0.35P 	UCIVUS0 	35 	P
916148-010-CIVUS 	916148 	010 	CIVUS
916425-010-CIVUS 	916425 	010 	CIVUS
257198-010-CIVUS 	257198 	010 	CIVUS
916425-010-CIVUS 	916425 	010 	CIVUS
7116-7004-08-UCIVUS 	7004 	08 	UCIVUS
916148-010-UCIVUS 	916148 	010 	UCIVUS