NULL/Blank Conversion

allanau20
allanau20 used Ask the Experts™
on
Hi Experts!

This here takes a numeric and removes the decimal and pad it w/zeros to the left -- total lenght is 9.

How to change it so that if @n is blank or NULL it returns 9 zeros.

declare @n numeric(11,5)

SET  @n = '4.87100'

  print RIGHT('000000000' + REPLACE(CAST(LTRIM(RTRIM(@n)) AS VARCHAR(9)), '.', '') ,9)

TiA!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
 print RIGHT('000000000' + CASE WHEN @N IS NULL THEN '' ELSE REPLACE(CAST(LTRIM(RTRIM(@n)) AS VARCHAR(9)), '.', '') END ,9)
Top Expert 2010

Commented:
If @n is already delcared as numeric, then you should be able to use:

PRINT RIGHT('000000000' + CONVERT(varchar(9), CONVERT(int, COALESCE(@n, 0))), 9)

Author

Commented:
Thanks aneeshattingal -- Incorrect syntax near the keyword 'RIGHT'.

Thanks  matthewspatrick -- that doesn't work.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
my bad aneeshattingal -- forgot to 'print' in front of 'right'.

Forgot to inlcude that if number is '87100' then the return result should be 000087100 instead of 087100000

thanks again!
Top Expert 2010

Commented:
PRINT RIGHT('000000000' + CAST(CAST(COALESCE(@n, 0) * 100000 AS int) / 1000 AS varchar(11)), 9)
This should do it:

PRINT RIGHT('00000000' + CAST(FLOOR(CAST('0' + LTRIM(RTRIM(COALESCE(@n, '0'))) as DECIMAL(11,2))) AS VARCHAR(9)), 9)

Tested and works for:
NULL
''
'1.9123456'
'1'
'1.1'
Oops.  Ignore that.  I missed that it is stripping the decimal, not chopping off at the decimal.

Commented:
declare @n numeric(11,5)

SET  @n = '4.87100'

  print RIGHT('000000000' + REPLACE(CAST(LTRIM(RTRIM(COALESCE(@n, 0))) AS VARCHAR(9)), '.', '') ,9)

Author

Commented:
Hi All,

First, my bad I missed out that if there are zeros after the decimal
it should be removed and then the padding on the left side is with zeros up to length 9 total.

Thanks for your post, but none are correct.

here's a good sample testing cases:

declare @n numeric(11,5)
Declare @m numeric(11,5)
Declare @o numeric(11,5)
Declare @p numeric(11,5)
Declare @q numeric(11,5)
Declare @r numeric(11,5)

SET  @m =  '04.87100'
SET  @n =  '4.87100'
SET  @o =  '48.71'
SET  @p = '4871'
SET  @q = '00000'
SET  @r = NULL

Results should be:

SET  @m =  '04.87100' --> 000004871
SET  @n =  '4.87100'  --> 000004871
SET  @o =  '48.71'    --> 000004871
SET  @p = '4871'      --> 000004871
SET  @q = '00000'     --> 000000000
SET  @r = NULL        --> 000000000
If blank then 000000000

aneeshattingal:

if @n='48.71' then it returns '004871000'. It should return '000004871'
if @n='4871' then it returns '048710000'. It should return '000004871'

it works for NULL and blank

matthewspatrick:

Doesn't work if @n = digits ie: 48712
if @n='4871' then it returns '000487100'. It should return '000004871'
 
wilje:

if @n='48.71' then it returns '004871000'. It should return '000004871'
if @n='4871' then it returns '048710000'. It should return '000004871'

Again, sorry for the confusion and thanks for your help!

Author

Commented:
Please entertain this side question, if the data type is numeric can we assume there's a decimal pt if it's a whole number or 0?

I tried to search with this and couldn't get it to work:

select
CHARINDEX ( '.', CAST(PRICEX as varchar(11)),0), PRICEX
from tbl02
where CHARINDEX ( '.', CAST(PRICEX as varchar(11)),0) = '0'

If that's the case then we can ignore:

SET  @p = '4871'
SET  @q = '00000'

TiA!

Author

Commented:
nvm, read that if format is (11,5) then there's decimail pt following by 5 char.
AneeshDatabase Consultant
Top Expert 2009

Commented:
here you go

SELECT RIGHT(REPLICATE ('0', 9) +REPLACE( CAST(@n *10000000 as int),'0','') , 9)
 

Author

Commented:
Almost there ..

If we try w/these values:

= '4871.00001'
= '12345.00000'

we get this err msg:

  Arithmetic overflow error converting expression to data type int.
AneeshDatabase Consultant
Top Expert 2009

Commented:

SELECT RIGHT(REPLICATE ('0', 9) +CASE WHEN @n IS NULL THEN '0' ELSE REPLACE( CAST(@n *100000 as bigint),'0','')END , 9)

Author

Commented:
Almost there!

If = '4871.00001' then it returns 000048711. Should return 487100001
If = '123.001' then it returns 000001231. Should return 000123001
Database Consultant
Top Expert 2009
Commented:

SELECT RIGHT(REPLICATE ('0', 9) + CASE WHEN @n IS NULL THEN '0' ELSE REVERSE(CAST(REVERSE(CAST(@n *100000 as bigint)) as int)) END, 9)
HI,


try this:


declare @n numeric(11,5)
Declare @m numeric(11,5)
Declare @o numeric(11,5)
Declare @p numeric(11,5)
Declare @q numeric(11,5)
Declare @r numeric(11,5)
 
SET  @m =  '04.87100' --> 000004871
SET  @n =  '4.87100'  --> 000004871
SET  @o =  '48.71'    --> 000004871
SET  @p = '4871'      --> 000004871
SET  @q = '00000'     --> 000000000
SET  @r = NULL        --> 000000000
 
SELECT  'm',RIGHT('000000000' + CONVERT(VARCHAR,CONVERT(INT,REPLACE(CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(@m,0 ))),'.',''))),9)
UNION
SELECT  'n',RIGHT('000000000' + CONVERT(VARCHAR,CONVERT(INT,REPLACE(CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(@n,0 ))),'.',''))),9)
UNION
SELECT  'o',RIGHT('000000000' + CONVERT(VARCHAR,CONVERT(INT,REPLACE(CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(@o,0 ))),'.',''))),9)
UNION
SELECT  'p',RIGHT('000000000' + CONVERT(VARCHAR,CONVERT(INT,REPLACE(CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(@p,0 ))),'.',''))),9)
UNION
SELECT  'q',RIGHT('000000000' + CONVERT(VARCHAR,CONVERT(INT,REPLACE(CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(@q,0 ))),'.',''))),9)
UNION
SELECT  'r',RIGHT('000000000' + CONVERT(VARCHAR,CONVERT(INT,REPLACE(CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(@r,0 ))),'.',''))),9)

Open in new window

Here's a UDF that should handle all cases without rounding issues (as can come from FLOAT casts).  I tested with the following queries:

SELECT dbo.ufn_ReturnNumStr('04.87100')
    Returned:  000004871

SELECT dbo.ufn_ReturnNumStr('4871')
    Returned:  000004871

SELECT dbo.ufn_ReturnNumStr('048710')
    Returned:  000048710

SELECT dbo.ufn_ReturnNumStr('48710.010')
    Returned:  004871001

SELECT dbo.ufn_ReturnNumStr('')
    Returned:  000000000

DECLARE @s VARCHAR(11)
SELECT dbo.ufn_ReturnNumStr(@s)      -- NULL
    Returned:  000000000


ALTER FUNCTION dbo.ufn_ReturnNumStr (
	@n VARCHAR(11)
	)
RETURNS VARCHAR(9)
AS
BEGIN
	DECLARE @done BIT
 
	SET @done = 0
	SET @n = COALESCE(@n, '')
	IF CHARINDEX('.', @n) > 0
	BEGIN
		WHILE LEN(@n) > 1 AND @done = 0
		BEGIN
			IF RIGHT(@n, 1) = '0'
				SET @n = LEFT(@n, LEN(@n)-1)
			ELSE
				SET @done = 1
		END
	END
	RETURN RIGHT('000000000' + REPLACE(@n, '.', ''), 9)
END

Open in new window

Author

Commented:
good try jyparask: it didn't work for this: '4871.00001'

Eureka aneeshattingal and bhess1 -- that did it!

Really appreciate your udf bhess1 -- didn't look easy to code. Unfortunately, I've give aneeshattingal  the pts. Hope you'll understand.

thanks y'all!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial