CODEWITHSUNDEEP
sqlsql-serverconcatenation
Hector Marcia
Hector Marcia

Reputation: 103

SQL Server : drop zeros from col1 and concat with col2 into new View

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

Upvotes: 0

Views: 105

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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.

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 12969

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 |
+------------+------+------+

Upvotes: 1

Related Questions