Menu
I have two fields in a table. One contains values such as BTA, BEA, REA. The other contains values such as 1,2,63,103.
I want to combine the 2 fields so they look like this BTA001, BTA002, BTA063, BTA103.
Notice how if the numbers are not 3 characters in length, I want to pad some 0's to the left of the number to make it equal to 3.
How would I go about doing this? The fields in the table are called Type which correspond to BTA, BEA, and REA and Id is the field that corresponds to 1, 2, 63, and 103.
574
Oracle has a TO_CHAR(number) function that allows us to add leading zeros to a number. It returns its result as a string in the specified format. The 0 format element is what outputs the leading zeros. If we didn't want leading zeros, we could use 9 .
When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL . When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string).
The CONCAT() function adds two or more strings together. Note: See also Concat with the + operator and CONCAT_WS().
Another way to implement Concat in SQL with the numerical value is to use the CAST operator. This operator converts the numerical data into the string format. Using the + (plus) operator will manipulate the numeric data into string concatenation.
select Type + right('00' + cast(id as varchar(10)), 3)
from ...
Edit: if id can be null and you would like a zero to show, you can do:
select Type + right('00' + cast(isnull(id, 0) as varchar(10)), 3)
from ...
select C1 + right(('000' + cast(C2 as nvarchar(10)),3) as
from t1
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
