Concatenate and add a character to integer columns in SQL

Question

I have a 10 Character length values in a column in SQL Server. I need to split that column at fixed length and remove the leading zeros and add a - after each of the values. I am able to split the values by using Substring and converting them to int. It is working well.

However, when I try to concatenate it is failing. Appreciate if you can help.

SELECT TOP 1 R.COL1, CAST(SUBSTRING(R.COL1,1,1) AS int) AS F1,CAST(SUBSTRING(R.COL1,2,5) AS int) AS F2,CAST(SUBSTRING(R.COL1,7,4) AS int) AS F3 CAST(SUBSTRING(R.COL1,1,1) AS int) +'-' +CAST(SUBSTRING(R.COL1,2,5) AS int)  +'-' + CAST(SUBSTRING(R.COL1,7,4) AS int) AS finalString  FROM MYTABLE R

If the value for COL1 IS 1012950001 the finalString I am expecting is 1-1295-1 however the result I am getting from the above query is 1297 as it is adding all the values. Appreciate if you can help.

Don't cast them to int if you don't want + to treat them as ints — Dave Costa
– Dave Costa, Commented Jun 20, 2020 at 14:05
Converting, however, is the easiest way to remove the leading zeroes though, @DaveCosta . The alternative would be using PATINDEX and STUFF. — Thom A
– Thom A ♦, Commented Jun 20, 2020 at 14:10
Yes, however, how does that help remove leading zeroes, @DaveCosta ? — Thom A
– Thom A ♦, Commented Jun 20, 2020 at 16:53

Thom A · Accepted Answer · 2020-06-20 14:04:47Z

3

You can't use the + operator with a numerical data type and a varchar that cannot implicitly be converted to that data type. Something like 1 + 'a' isn't going to work, as 'a' isn't an int, and can't be implicitly converted to one.

If you are mixing data types, then use CONCAT, which implicitly converts each part into a (n)varchar:

CONCAT({Numerical Expression},'a',{Other varchar Expression})

answered Jun 20, 2020 at 14:04

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ranjit Singh · Accepted Answer · 2020-06-20 14:58:57Z

0

You can use concat method to concatenate the substring value

select 
concat(CAST(SUBSTRING('1012950001',1,1) AS int), '-', 
CAST(SUBSTRING('1012950001',2,5) AS int), '-', 
CAST(SUBSTRING('1012950001',7,4) AS int)) AS finalString

This will give you the expected result '1-1295-1'

answered Jun 20, 2020 at 14:58

Ranjit Singh

3,7351 gold badge23 silver badges36 bronze badges

Collectives™ on Stack Overflow

Concatenate and add a character to integer columns in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related