zero padding - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

zero padding

Author

Topic

pingili07656
Starting Member

3 Posts

Posted - 2011-09-23 : 18:59:18

Hi all,

I am new to SQL programming.

I have a column named combined_hierarchy
with values in x.xx.xx.xx.x format . Example 7.19.08.86.1
I need to change it to x.xx.xx.xx.xxx format. that would be 7.19.08.86.001 format.
and 7.19.08.86.71 would be 7.19.08.86.071

Any help is appriciated.

Thanks
Sneha

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-23 : 22:00:18

Hello Sneha,

Perhaps something like;

DECLARE @combined_hierarchy VARCHAR(20) = '7.19.08.86.1'
SELECT LEFT(@combined_hierarchy,11) + RIGHT('000' + SUBSTRING(@combined_hierarchy,12,LEN(@combined_hierarchy)),3)

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-24 : 06:05:34

Sneha,

These are the 3 different ways by which we can pad leading zeros to a string:

DECLARE @combined_hierarchy VARCHAR(15)
SET @combined_hierarchy = '7.19.08.86.71'

--Method 1:
SELECT LEFT(@combined_hierarchy,11) +
RIGHT('000' + SUBSTRING(@combined_hierarchy,12,LEN(@combined_hierarchy)),3)

--Method 2:
Select LEFT(@combined_hierarchy,11) +
REPLACE(STR(SUBSTRING(@combined_hierarchy,12,LEN(@combined_hierarchy)), 3), ' ', '0')

--Method 3:
Select LEFT(@combined_hierarchy,11) +
Right(Replicate(0, 3) + Convert(VARCHAR(3), SUBSTRING(@combined_hierarchy,12,LEN(@combined_hierarchy))), 3)

Best Regards
Vadivel

http://vadivel.blogspot.com

Kristen
Test

22859 Posts

Posted - 2011-09-24 : 06:53:25

The first part is a fixed format, right? you don't have

x.xx.xx.xx.x
and also
xx.xx.xx.xx.x

type variations? because you said your format was
x.xx.xx.xx.x
and then gave an example for
x.xx.xx.xx.xx

if the leading part is a fixed 11 characters then this will do


SELECT	STUFF(combined_hierarchy, 12, 0, REPLICATE('0', 14-LEN(combined_hierarchy)))
FROM
(
	SELECT	[combined_hierarchy] = '7.19.08.86.1' UNION ALL
	SELECT	'7.19.08.86.12' UNION ALL
	SELECT	'7.19.08.86.123'
) AS X

if you want padding after the last ".", which may be in a variable position, then this:


SELECT	STUFF(
		combined_hierarchy, 
		LEN(combined_hierarchy) - CHARINDEX('.', REVERSE(combined_hierarchy)) + 2,
		0, 
		REPLICATE('0', 4-CHARINDEX('.', REVERSE(combined_hierarchy)))
	)
FROM
(
	SELECT	[combined_hierarchy] = '7.19.08.86.1' UNION ALL
	SELECT	'7.19.08.86.12' UNION ALL
	SELECT	'17.19.08.86.1' UNION ALL
	SELECT	'17.19.08.86.12' UNION ALL
	SELECT	'127.19.08.86.123' UNION ALL
	SELECT	'7.119.08.86.1' UNION ALL
	SELECT	'7.119.08.86.12' UNION ALL
	SELECT	'7.119.08.86.1' UNION ALL
	SELECT	'7.119.08.86.12' UNION ALL
	SELECT	'7.119.08.86.123' UNION ALL
	SELECT	'17.119.08.86.1' UNION ALL
	SELECT	'17.119.08.86.12' UNION ALL
	SELECT	'17.119.08.86.123' UNION ALL
	SELECT	'127.119.08.86.1' UNION ALL
	SELECT	'127.119.08.86.12' UNION ALL
	SELECT	'127.119.08.86.123'
) AS X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources