March 16, 2009 at 12:47 pm
hi
i have one column that Retrieve the Value from the Excel Column which contan the Numbers,,whose length are not identical ..
like
123
12345
123456
now i want to add prefix of 0 to make this column as Identical ,,
000000123 (9 digit)
000012345 ( 9 digit)
000123456 ( 9 digit)
i was trying to handle it on Derived Column..
Please provide help..
March 16, 2009 at 12:59 pm
Hi
You can use the REPLICATE function:
DECLARE @t TABLE (txt VARCHAR(100))
INSERT INTO @t VALUES('123')
INSERT INTO @t VALUES('12345')
INSERT INTO @t VALUES('123456')
SELECT txt, REPLICATE('0', 9 - LEN(txt)) + txt
FROM @t
Greets
Flo
March 16, 2009 at 10:44 pm
Or:
Right('000000000' + Cast(num as varchar(10)), 9)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 18, 2009 at 8:31 am
SELECT REPLACE(STR(123, 9), ' ', '0')
generates an output like this...
000000123
March 18, 2009 at 11:39 am
THNX a lot it worked out.
March 18, 2009 at 11:40 am
THNX a lot guys for your reply
it worked out.
March 18, 2009 at 11:55 am
select [Nine Digits] = right(1000000000+num,9)
from
( --Test Data
select num = 456 union all
select num = 3456 union all
select num = 123456
) a
Results:
Nine Digits
-----------
000000456
000003456
000123456
March 18, 2009 at 7:29 pm
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 18, 2009 at 8:26 pm
If anyone is interested in the most efficient way to do it, take a look at performance tests on this this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=122003
And no, the most effiecient wasn't the solution I posted on this thread. :blush:
March 18, 2009 at 9:07 pm
If you had that time machine you could fix that, you know. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 18, 2009 at 9:34 pm
I didn't test every possible method, so who knows if something better will turn up.
Don't speak too soon for the wheel's still in spin and there's no tellin' who that it's namin'
Sincere apologies to Bob.
March 19, 2009 at 1:38 pm
I have a function that I use to pad with zeros that works well;
--//
create function [dbo].[fn_PadWithZeros]
(@val decimal(18,2), @len int)
returns varchar(100)
as
begin
declare @cval varchar(4000), @i int
set @i=isnumeric(@val)
if @i=0
return replace(space(@len),space(1),'0')
set @cval=
replace(str(@val,@len),space(1),'0')
return @cval
end
--//
Hope it helps. Grady Christie
March 19, 2009 at 1:46 pm
Hi Christine
Maybe you should change:
replace(space(@len),space(1),'0')
with:
REPLICATE('0', @len)
... to avoid to much memory copying in server.
Greets
Flo
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply