Is there a way to use the column bar that I made in mssql select?

SELECT D.dev_serial"
    ,D.dev_name
    /* /* location */
    ,case when charIndex(']', dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1) , '_', 3)) > 0 then dbo.funcSplit(dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1) , '_', 3), ']', 2)
          when charIndex(']', dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1) , '_', 3)) = 0 then dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1) , '_', 3)
          when len(dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1), '-', 2)) < 5 then dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), '(', 1)
          when dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1) , '_', 2) = NULL then D.dev_name
          when dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1) , '_', 3) = NULL then dbo.funcSplit(dbo.funcSplit(D.dev_name, '(', 1) , '_', 2)
          else D.dev_name end as location
    /* /* location_code */
    ,case when len(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)) = 2 then dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)
          when len(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)) = 3 then dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)
          when len(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)) = 4 then dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)
          when len(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)) = 6 then dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)
          when len(dbo.funcSplit(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1), ']', 2)) > 3 then left(dbo.funcSplit(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1), ']', 2), 3)
          when CHARINDEX(']', dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)) > 0 then dbo.funcSplit(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1), ']', 2)
          when dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1) = NULL then D.dev_name
          when len(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)) = 0  then dbo.funcSplit(D.dev_name, ' ', 2) 
          when len(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1)) > 18 then left(dbo.funcSplit(dbo.funcSplit(D.dev_name, '_', 3), ' ', 1),3)
          else D.dev_name end as location_code 

FROM cheonan01.vms_device D

When you create it like this, a structure like the picture below is created. However, in order to put the information of the location_code into English, I would like to use the location_code directly in the select statement and accumulate the information converted into 'Banpo-dong' when it is 'Banpo-dong' in a column called location_code_eng.

Is there a way?

mssql sql mysql

2022-09-20 22:05

1 Answers

You can write (temporary) table with the Korea-English matching information.

CREATE TABLE #location_names(
    ko varchar(50),
    en varchar(100)
);
INSERT INTO location_names
(ko, en) VALUES
('Banpo-dong', 'Banpo-dong'),
('Daepo-dong', 'Taepo-dong');

WITH locations AS (
    -- Enter all the SELECT statements you posted here
)
SELECT l.*, ln.en AS location_code_eng
FROM locations l
LEFT JOIN location_names ln ON ln.ko = l.location_code;

2022-09-20 22:05

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656