Clash Royale CLAN TAG#URR8PPP

Split a column in two based based on variable lenght field

Hi: I have a table made with rows like this:

ID_CATEGORIA CATEGORIA_DRG
------------ ---------------------------------------------------------------
1 001-002-003-543 Craniotomia
2 004-531-532 Interventi midollo spinale
3 005-533-534 Interventi vasi extracranici
4 006 Decompressione tunnel carpale


I'd like to get something like this:

ID CATEGORIA DESCRIZIONE
------------ ------------------ --------------------------------------
1 001-002-003-543 Craniotomia
2 004-531-532 Interventi midollo spinale
3 005-533-534 Interventi vasi extracranici
4 006 Decompressione tunnel carpale


I don't need to alter the table, a 'formatted' query can be enough.

I Think SUBSTRING() is the right function for me, but I don't know how to mesaure the lenght of the first (numbers, dash-separated) field.

SUBSTRING()

In Python I'll find that size with len("005-533-534 Interventi vasi extracranici".split(' ')[0])', but I don't have idea about how to write it in SQL

len("005-533-534 Interventi vasi extracranici".split(' ')[0])

2 Answers
2

Something like this should do -

SELECT ID_CATEGORIA AS ID ,SUBSTRING(CATEGORIA_DRG,1,CHARINDEX(' ',CATEGORIA_DRG)) as CATEGORIA,SUBSTRING(CATEGORIA_DRG,CHARINDEX(' ',CATEGORIA_DRG),LEN(CATEGORIA_DRG)) AS DESCRIZIONE
FROM TABLENAME


Try this:

select id_categoria ID,
substring(categoria_drg, 1, idx) CATEGORIA,
substring(categoria_drg, idx + 1, 1000) DESCRIZIONE
from (
select id_categoria, categoria_drg, charindex(' ', categoria_drg) idx from my_table
) a


It uses charindex to detect when the code is finished, because it is followed by first space in the string, which the function finds :)

charindex

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.