0

I need to reconcile article1 (top) and article2 tables into a View displaying differences. But before that I need to drop all zeros from column 'type'. Create new ID column equals to filenumber + type so the resulting column should be use as index. All columns share same data type

Columns needed:

ID
C0016
C0029
C00311

enter image description here

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hector Marcia
  • 103
  • 1
  • 12

2 Answers2

1

You can utilize below script in SQL Server to get the format you want:

Reference SO post on removing padding 0

SELECT CONCAT(filenumber,type) AS filenumber, type, cost
FROM
(
SELECT
filenumber,
SUBSTRING(type, PATINDEX('%[^0]%',type), 
                   LEN(type)- PATINDEX('%[^0]%',type)+ 1) AS type, cost
FROM 
(
 VALUES 
  ('C001','00006',40),
   ('C002','00009',80),
   ('C003','00011',120)
  ) as t(filenumber,type, cost)
 ) AS t

Resultset 

+------------+------+------+
| filenumber | type | cost |
+------------+------+------+
| C0016      |    6 |   40 |
| C0029      |    9 |   80 |
| C00311     |   11 |  120 |
+------------+------+------+
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
1

You can use try_convert() :

alter table table_name
    add id as concat(filenumber, try_convert(int, type)) persisted -- physical storage

If you want a view :

create view veiw_name 
as 
   select t.*, concat(filenumber, try_convert(int, type)) as id
   from table t;

try_convert() will return null whereas conversation fails.

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • the above does the conversion, creates the new column but with 6 spaces between the two values. i.e. line one ID value is 'C001 6' – Hector Marcia Mar 20 '20 at 13:17