Click here to Skip to main content
14,696,432 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
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:

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:
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
Updated 22-Sep-20 3:49am

You were pretty close to find solution...

;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
   
Comments
Sincobile Nhlengethwa 22-Sep-20 9:04am
   
Thank you so much @Maciej Los this is what i wanted
Maciej Los 22-Sep-20 9:06am
   
You're very welcome.
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!
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900