split 1 column into 2 in sql server giving update

Question

I use SQL Server 2012

I have the following problem, in a table there are records of municipalities and two respective Fus but there is the need to divide this column into 2 one of municipality and one of FU, but the data are this way:

CANDÓI-PR
VERÊ-PR
NAO-ME-TOQUE-RS
NAO-ME-TOQUE / RS
SERTAO / RS
JABOTICABA/RS
VERÊ

So if someone can create a code that separates this into 2 and doing an UPDATE in the original, taking into account that there is no pattern in the column for example there is '/' '-' and there are some without Ufs that should return NULL

tried the following code:

select 
    case when CHARINDEX('-',[MUNICIPIO / UF])>0 
         then SUBSTRING([MUNICIPIO / UF],1,CHARINDEX('-',[MUNICIPIO / UF])-1) 
         else [MUNICIPIO / UF] end Municipio, 
    CASE WHEN CHARINDEX('-',[MUNICIPIO / UF])>0 
         THEN SUBSTRING([MUNICIPIO / UF],CHARINDEX('-',[MUNICIPIO / UF])+1,len([MUNICIPIO / UF]))  
         ELSE NULL END as UF
from TABLE
UNION all
select 
    case when CHARINDEX('/',[MUNICIPIO / UF])>0 
         then SUBSTRING([MUNICIPIO / UF],1,CHARINDEX('/',[MUNICIPIO / UF])-1) 
         else [MUNICIPIO / UF] end Municipio, 
    CASE WHEN CHARINDEX('/',[MUNICIPIO / UF])>0 
         THEN SUBSTRING([MUNICIPIO / UF],CHARINDEX('/',[MUNICIPIO / UF])+1,len([MUNICIPIO / UF]))  
         ELSE NULL END as UF
from TABLE

They work separately but together return duplicate values and distinct does not help because there really are equal values in the table.

Answer 1

Try this script...

CREATE TABLE #TMP_CIDADES
(
    [MUNICIPIO / UF] varchar(max) NOT NULL
)

INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('CANDÓI-PR')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('VERÊ-PR')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('NAO-ME-TOQUE-RS')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('NAO-ME-TOQUE / RS')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('SERTAO / RS')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('JABOTICABA/RS')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('VERÊ')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('VERÊ NOVA    - SP')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('VERÊ VELHA     / RJ')
INSERT INTO #TMP_CIDADES ([MUNICIPIO / UF]) VALUES ('VERÊ      -        AM')

DECLARE @LETRAS_ESTADOS varchar(26) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

SELECT 
    CASE WHEN CHARINDEX(SUBSTRING(REVERSE(REPLACE([MUNICIPIO / UF], ' ', '')), 3, 1) ,@LETRAS_ESTADOS) = 0 THEN REPLACE(REPLACE(REVERSE(SUBSTRING(REVERSE(REPLACE([MUNICIPIO / UF], ' ', '')), 1, 3)), '-', ''), '/', '') ELSE NULL END ESTADO
    ,CASE WHEN CHARINDEX(SUBSTRING(REVERSE(REPLACE([MUNICIPIO / UF], ' ', '')), 3, 1) ,@LETRAS_ESTADOS) = 0 THEN SUBSTRING([MUNICIPIO / UF], 1, CHARINDEX(SUBSTRING(REVERSE(REPLACE([MUNICIPIO / UF], ' ', '')), 3, 1),[MUNICIPIO / UF]) - 1) ELSE [MUNICIPIO / UF] END CIDADE
FROM #TMP_CIDADES

DROP TABLE #TMP_CIDADES

With TEMP Loaded and after checking the conversions, you can update from it.