2
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.
guy thanks! It was missing a case that I just saw now where the appears SEE RS, only with space but it I solve!
– unkbr