experssion in a view to pad out column lenght

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)

experssion in a view to pad out column lenght

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-12-07 : 10:56:22

Hi i like to use an expression in a view i'd like to apply this logic in it

if the string lenght is = 13 in lenght add a 0 else don't do anything i want to pad my colum out to 14 digits if they are 13 digits long.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-07 : 10:58:56

left pad
right('00000000000000' + convert(varchar(14),myval),14)
right pad
left(convert(varchar(14),myval) + '00000000000000',14)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 11:03:26

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93818

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-07 : 11:12:50

If you are sure that the lenght is either 13 0r 14 then

left pad
right('0' + convert(varchar(14),myval),14)
right pad
left(convert(varchar(14),myval) + '0',14)

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-12-07 : 11:29:44

The problem am having is that i only want to put a extra 0 on to the lenght of the string if the lenght is 13 other wise leave it alone, i was just doing this in the view

'0'+ col_name

but i only want to add the 0 if the string lenght is 13 digits

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 11:31:25

Both nr and madhivanan have shown you plenty of examples how to accomplish this.

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-07 : 11:32:01

did you try
right('00000000000000' + col_name,14)
or
right('0' + col_name,14)

It will work for both 13 and 14 digits.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-12-07 : 11:41:52

I was only making the problem more clear, in my last reply i got both solutions to work thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources