How to format numeric field into varchar while maintaining some decimal places 1

[roena26]

We're using SQL Server 2008 R2. I inherited an automated process that sends data to a third-party outside vendor via an interface and the data is generated through a SQL query. I need to update the query and provide an unique identifier by concatenating three fields: id, package_size, and package_unit.

id is numeric(18,0)​

package_size is numeric(12,3)​

package_unit is varchar(254)​

The resulting column needs to be id + package_size + package_unit

Some sample data:

id        package_size     package_unit
97716     NULL             mL
106724    4.000            mL
NULL      NULL             EACH
121161    1.000            EACH
2508      0.250            EACH
10580     11.250           mL

I've used CONCAT and CAST to create the following code:

CONCAT((CAST(id AS VARCHAR(15))), (CONCAT((CAST(package_size AS VARCHAR(15))),(package_unit))))

which results in the following:

97716mL
1067244.000mL
EACH
1211611.00EACH
25080.250EACH
1058011.250mL

Unfortunately, there are a lot of fields which are not populated so I will have to come up with something for the NULL values.

What I would like to be able to do is to format the package_size field better. So if the package size was 4.000, it would display as 4. If the package_size was 0.250, it would display as 025, etc.

Specifically,

1067244.000mL     would be      1067244mL
1211611.00EACH                  1211611EACH
25080.250EACH                   2508025EACH
1058011.250mL                   105801125mL

I can be okay with keeping the trailing zeros (4000 instead of 4) but I really need to get rid of the decimal point and keeping the leading zero if at all possible.

Any suggestions?

 

[jjefferson]

To just get rid of the decimal, you could use REPLACE:

REPLACE(CONCAT((CAST(id AS VARCHAR(15))), (CONCAT((CAST(package_size AS VARCHAR(15))),(package_unit)))), '.', '')

 

[gmmastros]

try this:

Declare @Temp Table(Id numeric(18,0), package_size numeric(12,3), package_unit varchar(254))

insert into @Temp Values(97716 ,    NULL    ,'mL')
insert into @Temp Values(106724,    4.000   ,'mL')
insert into @Temp Values(NULL  ,    NULL    ,'EACH')
insert into @Temp Values(121161,    1.000   ,'EACH')
insert into @Temp Values(2508  ,    0.250   ,'EACH')
insert into @Temp Values(10580 ,    11.250  ,'mL')

Select  Coalesce(Convert(VarChar(18), Id), '')
        +
        Coalesce(Replace(Replace(RTrim(Replace(Convert(VarChar(12), package_size), '0', ' ')), ' ', '0'), '.', ''), '')
        + package_unit
From    @Temp

This code above puts data in to a table variable so that I can test the code I recommend here. If you are satisfied with it, then it should be easy enough to adapt it to your purposes.

The "trick" with this code is dealing with the numeric data. Based on your sample data and expected results, you want trailing zero's removed. The code I use basically converts to string, replace 0 with (space), Right Trims, replace space with 0, then replace decimal point with nothing.

Ex:

Suppose your package size is 101.250.

Select Convert(VarChar(12), 101.250) -- Result is "101.250"
Select Replace('101.250', '0', ' ')  -- Result is "1(space)1.25(space)"
Select RTrim('1 1.25 ')              -- Result is "1(space)1.25"
Select Replace('1 1.25', ' ', '0')   -- Result is "101.25"
Select Replace('101.25', '.','')     -- Result is "10125"

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

Small correction to Georges code as you said you wished to keep the leading zero e.g. 0.250 should be shown as 025

Select  Coalesce(Convert(VarChar(18), Id), '')
        + case
          when package_size > -1 
           and package_size < 1
           then '0'
          else ''
          end
        +
        Coalesce(Replace(Replace(RTrim(Replace(Convert(VarChar(12), package_size), '0', ' ')), ' ', '0'), '.', ''), '')
        + package_unit
From    @Temp

But regarding what you say about " unique identifier by concatenating three fields"

how will you deal with the situation below?

id	package_size	package_unit	expected string (not unique)
970     25              mL              97025mL
97      0.25            mL              97025mL

Normally when trying to generate unique identifiers by concatenating fields together it is advisable to separate them with another character (/-;, for example) precisely to avoid the situations like the above.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

Frederico,

I'm curious. How did you decide that my code needed a correction? Please understand that I don't think I never make mistakes. I do, all the time. In this case, I tested the squirrely string gymnastics to make sure I got it right.

I'm running this on SQL2005....

Declare @Temp Numeric(12,3)

Set @Temp = 0.25

Select Coalesce(Replace(Replace(RTrim(Replace(Convert(VarChar(12), @Temp), '0', ' ')), ' ', '0'), '.', ''), '')

The OP says that package_size is numeric(12,3), so I created a variable with the same data type, set it's value to 0.25, and then ran it through the code. The result is 025, just like to OP wanted, and contrary to what you imply. The only reason I ask is because I respect you and your sql skills. I think this may be a learning opportunity for one of us.

Your second point about possibly duplicating the unique identifier occurred to me as well. I'm glad you mentioned it because it could easily happen with real data.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

George - my sincere apologies.

I misread the example code (keep confusing trailing/leading zeros), and did not try the real one, so lesson learned here is always try it before commenting.

Respect is mutual as you know so also appreciative of how you addressed my error.

Thanks

Frederico

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[roena26]

But regarding what you say about " unique identifier by concatenating three fields"

how will you deal with the situation below?

id	package_size	package_unit	expected string (not unique)
970     25              mL              97025mL
97      0.25            mL              97025mL

Normally when trying to generate unique identifiers by concatenating fields together it is advisable to separate them with another character (/-;, for example) precisely to avoid the situations like the above.

That's a very good question that I'll have to bring back to the team as I don't think the decision to concatenate fields was thoroughly investigated. Thanks for the heads up.

 

[roena26]

Thank you, gmmastros, that worked for the straightforward items. I appreciate your help.

 

[gk53]

if you want separator this is sample with |

Declare @Temp Table
(
	Id numeric(18,0), 
	package_size 
	numeric(12,3), 
	package_unit varchar(254)
)

insert into @Temp Values(97716 ,    NULL    ,'mL')
insert into @Temp Values(106724,    4.000   ,'mL')
insert into @Temp Values(NULL  ,    NULL    ,'EACH')
insert into @Temp Values(121161,    1.000   ,'EACH')
insert into @Temp Values(2508  ,    0.250   ,'EACH')
insert into @Temp Values(10580 ,    11.250  ,'mL')


Select  isnull(Convert(VarChar(18), Id),'') + '|'
        +  isnull(Replace(Replace(RTrim(Replace(Convert(VarChar(12), package_size), '0', ' ')), ' ', '0'), '.', ''),'') + '|'
        + isnull(package_unit, '')
From    @Temp