add '0' before the string - 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)

add '0' before the string

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 03:16:46

example

if i have string '765' i want 4 zero befor -> '0000765'
if i have string '765432' ->'0765432'

save of length of 7.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 03:24:28

[code]select right('0000' + yourstringfield,7) from yourtable [/code]

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 03:28:42

ok,but i dont know how many zeros i need to add

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 03:30:57

and it need to be left,isnt it?

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-01 : 03:31:37

hi

try like this
select right('0000000' + '23',7)

ok

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 03:33:09

quote:
Originally posted by inbs

ok,but i dont know how many zeros i need to add

it depends on how many digts you want in resultset. add that many zeroes to left of value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 03:34:10

quote:
Originally posted by inbs

and it need to be left,isnt it?

nope. it needs to be right. the zeroes are prefixed towards left and you take rightmost 7 characters in result

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 03:41:59

explain again:

how am i write how many '0' to adds?
because it change from one row to another.

as i show in previous example,

case
len(filed1)<7
add 7-len(field1) '0' +fiels
else
field1
end

thanks

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-01 : 03:46:42

hi inbs
just use seven 0's, as you need 7 column value

select right('0000000' + yourstringfield,7) from yourtable

ok

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 03:51:15

quote:
Originally posted by inbs

explain again:

how am i write how many '0' to adds?
because it change from one row to another.

as i show in previous example,

case
len(filed1)<7
add 7-len(field1) '0' +fiels
else
field1
end

thanks

you dont have to be worried about how many digits each value contains just use
right('0000000' + digitvalue,7) and it will always take last 7 digits including that amount of 0's

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 03:54:26

i stil output t digits of zero.
'0000000'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 03:55:48

see this for an example


DECLARE @Test table
(Strval varchar(7)
)
INSERT INTO @Test
SELECT '113' UNION ALL
SELECT '2' UNION ALL
SELECT '11' UNION ALL
SELECT '1278' UNION ALL
SELECT '22289' UNION ALL
SELECT '921321' UNION ALL
SELECT '1123' UNION ALL
SELECT '1312343' UNION ALL
SELECT '1123' 

select right('0000000'+ strval,7) from @test

output
----------------------
0000113
0000002
0000011
0001278
0022289
0921321
0001123
1312343
0001123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 03:59:48

quote:
Originally posted by inbs

i stil output t digits of zero.
'0000000'

t digits? where will get t from? is it passed as a param? i'm really not understanding what you're telling here.DO you mean your resultsets number of digits might vary dynamically? maybe some data from your tables might help.

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 04:01:32

ok, i succeed, sorry.

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 04:03:44

visakh16 ,how in india today?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 04:19:05

quote:
Originally posted by inbs

visakh16 ,how in india today?

Everything alright now

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-01 : 04:37:47

If data length is atleast a digit, you need only six zeroes
select right('000000'+ strval,7) from @test

Madhivanan

Failing to plan is Planning to fail

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-12-01 : 04:50:17

madhivanan,it is fine how they write to me.

thanks

Kalpdurg
Starting Member

1 Post

Posted - 2011-12-12 : 04:56:52

Thanks A Lot.. It works fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources