--Migration
-- Ahol a városnév csak négy számjegy (irányítószám)
UPDATE c SET C_VAROS = ts.C_TELEPULESNEV
  ,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
FROM T_CIM c
  INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
  INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
  INNER JOIN T_TELEPULES ts ON ts.C_IRANYITOSZAM = c.C_IRANYITOSZAM
WHERE C_VAROS LIKE '[0-9][0-9][0-9][0-9]' AND C_CIMTIPUSA = 907 AND c.TOROLT= 'F'

-- Irszám településnév javítása
UPDATE c SET C_VAROS = LTRIM(RTRIM(SUBSTRING(C_VAROS, 5, 120)))
  ,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
FROM T_CIM c
  INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
  INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
WHERE C_VAROS LIKE '[0-9][0-9][0-9][0-9] _%' AND C_CIMTIPUSA = 907 AND c.TOROLT = 'F'

-- "Irszám, településnév" javítása
UPDATE c SET C_VAROS = LTRIM(RTRIM(SUBSTRING(C_VAROS, 6, 120)))
  ,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
FROM T_CIM c
  INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
  INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
WHERE C_VAROS LIKE '[0-9][0-9][0-9][0-9], _%' AND C_CIMTIPUSA = 907 AND c.TOROLT = 'F'

-- településnév (irszám)
UPDATE c SET C_VAROS = LTRIM(RTRIM(SUBSTRING(C_VAROS, 1, LEN(C_VAROS) - 6)))
  ,MODIFIER = 0, LASTCHANGED = GETDATE(), SERIAL += 1
FROM T_CIM c
  INNER JOIN T_TANEV tv ON tv.ID = c.C_TANEVID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
  INNER JOIN T_INTEZMENY i ON i.ID = tv.C_INTEZMENYID AND i.TOROLT = 'F'
WHERE C_VAROS LIKE '%_([0-9][0-9][0-9][0-9])' AND C_CIMTIPUSA = 907 AND c.TOROLT = 'F'