keep leading zeros - 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

SQL Server 2005 Forums

Transact-SQL (2005)

keep leading zeros

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-03-18 : 15:34:02

SELECT (CASE WHEN ssn IS NOT NULL THEN CONVERT(varchar, ssn, 10) ELSE 'NO SSN' END) 'SSN'
etc.

Unfortunately this statement drops leading zeros off the SSN. How can I modify it to keep the leading zeros?

Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-03-18 : 15:55:06

what is the datatype of SSN column?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

eevans
Starting Member

48 Posts

Posted - 2009-03-18 : 15:58:03

(numeric(10,0),null)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 15:59:52

A numeric value doesn't keep leading zeros.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 16:00:19

SELECT COALESCE(REPLACE(STR(SSN, 10, 0), ' ', '0'), 'NO SSN') AS SSN
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

eevans
Starting Member

48 Posts

Posted - 2009-03-18 : 16:04:33

That makes sense. Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-18 : 16:18:28

Here is another way that returns a string of varchar(9).

select
	SSN,
	coalesce(right(1000000000+ssn,9),'NO SSN') as NEW_SSN
from
	(-- Test Data
	select ssn = 004581944	union all
	select ssn = null
	) a

Results:
SSN         NEW_SSN   
----------- --------- 
4581944     004581944
NULL        NO SSN

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-03-18 : 16:23:23

Peso, I wish I would've known that trick earlier... I've been looking for something like that.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 16:29:34

MVJ's trick is cool too and works well with integer data.

E 12°55'05.63"
N 56°04'39.26"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-18 : 17:02:48

I've never done any performance testing on variations of the different "leading zero" methods.

Maybe someday if I'm really, really bored...

I actually only post that method because it's very short to code. There could be problems with negative numbers, but I think the other methods will have trouble too.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-18 : 17:36:45

I think the test results below with 3 million rows of data show that the "normal" method is the fastest way to do the conversion.


-- Load Test Data
drop table #t
create table #t (num int primary key clustered)

insert into #t (num)
select
	num = number
from
	-- Number Table Function available here:
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
	f_table_number_range(0,3000000)
order by
	number

go

declare @st datetime, @cnt int
set @st = getdate()

select
	@cnt=count(*)
from
	#t
where
	num<> num

select elapsed_ms_init = datediff(ms,0,getdate()-@st)


go
declare @st datetime, @cnt int
set @st = getdate()


select
	@cnt=count(*)
from
	#t
where
	right(1000000000+num,9) > '9999999999'

select elapsed_ms_mvj = datediff(ms,0,getdate()-@st)

go

declare @st datetime, @cnt int
set @st = getdate()


select
	@cnt=count(*)
from
	#t
where
	REPLACE(STR(num, 10, 0), ' ', '0')  > '9999999999'

select elapsed_ms_peso = datediff(ms,0,getdate()-@st)

go
declare @st datetime, @cnt int
set @st = getdate()


select
	@cnt=count(*)
from
	#t
where
	right('000000000'+convert(varchar(9),num),9)  > '9999999999'

select elapsed_ms_normal = datediff(ms,0,getdate()-@st)

Results:


(3000001 row(s) affected)

elapsed_ms_init 
--------------- 
143

(1 row(s) affected)

elapsed_ms_mvj 
-------------- 
3156

(1 row(s) affected)

elapsed_ms_peso 
--------------- 
3750

(1 row(s) affected)

elapsed_ms_normal 
----------------- 
486

(1 row(s) affected)

CODO ERGO SUM

stepson
Aged Yak Warrior

545 Posts

Posted - 2009-03-19 : 04:52:37

REPLACE(STR(ISNULL(field, 0), 9), ' ', '0')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources