split 1 column into 2 on sql server giving update

Question

split 1 column into 2 on sql server giving update

Navigation

#1 by (1 votes)

2

I use SQL Server 2012

I have the following problem, in a table there are records of municipalities and two respective UFs, however the need arises to divide this column into two municipalities and one UF, but the data is like this:

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 in 2 and doing an UPDATE in the original, taking into account that there is no default in the column eg there is '/' '-' and there are some without UFs that should return NULL

I 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 they return duplicate values and the distinct does not help because there really are equal values in the table.

sql sql-server sql-update

asked by anonymous 12.07.2017 / 22:07

1 answer

How to change the color of the rows of a table according to the value of each table Error using date type on android

score 1 · Accepted Answer

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 Uploaded and after verifying the conversions, you can proceed with your update from it.