preappending zeroes - 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

preappending zeroes

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2006-04-13 : 18:38:02

Hi ,

Sorry for those that think this is obvious but I have tried for good time and cannot solve this problem in a simple way.

How can I append a 0 in the form of varchar to a 1 with datatype integer

What Im trying to do is add an alternate column to the identity column in my table.

The identityt collumn seeds in 0 all the way up with increments of 1.
ie 0,1,2,...,n
howver, I need that for the values below 10 a zero is preapended
ie
01,02,03,...,09, and then the sequence continues normally, ie 10,11,...,n. I am trying to preapend the zeroes to the mentioned dinumbers in the new calculated column I created without success.
this is what I would like tot do
select IdentityCoulmn,('0'+ IdentityCoulmn) as CalculatedCoulumn
from MyTable

but its not working the result comes out the same ie 1,2..n

thank you

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-04-13 : 18:43:53

What is the datatype of your calculated column? I think there's an implicit conversion happening to you. Try this:


select IdentityCoulmn,('0'+ CAST(IdentityCoulmn AS VARCHAR(9))) as CalculatedCoulumn
from MyTable

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 19:25:50

Are you trying to have a fixed length string for the CalculatedColumn ?
Something like 01, 02, .. 09, 10, 11 ?
What is max(IdentityColumn) ? 2 digits ? 3 digits ?

heze
Posting Yak Master

192 Posts

Posted - 2006-04-13 : 20:39:11

Hi ,

The source (identity) column is identity(0,1) not for replication created within a select into statement, therefore I had no chance to define the digit size.
The calculated column is/was int.
When I changed the int to varchar datatype it worked, however I had to use create table
instead of the original "select .. into"
However I used a quite cumbersome select case when identityfield<10 then '0' + cast(identityfield as varchar) else identityfeild end, which I find rather inefficient in case the size of the identity was say 3, 4..n digits as ktan explains.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 20:51:19

What is the purpose of the CalculatedColumn ?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-13 : 21:48:03

I'm trying to imagine an actual purpose for doing this.

Oh well, here's one way to do it:


select 
	MyNum =
	convert(varchar(10),replace(number*.1,'.',''))
from
	(select number from dbo.F_TABLE_NUMBER_RANGE(0,12) union all
	 select number from dbo.F_TABLE_NUMBER_RANGE(98,103) ) a
order by
	number


Results:

MyNum      
---------- 
00
01
02
03
04
05
06
07
08
09
10
11
12
98
99
100
101
102
103

(19 row(s) affected)

CODO ERGO SUM

heze
Posting Yak Master

192 Posts

Posted - 2006-04-13 : 23:45:35

Hi Yes, I am building a routine that creates a query on the fly and joins views according to the user's choices.
There are around 50 views and will have more. There is a naming convention in which the views are numbered according to predefined criteria.
eg, I build a string to join v_01,v_02,...,v_20. They name them 01 so that they appear sorted in the query analyzer. therefore the string is something like

while(iterator<=somenumber)
begin
if(some condition)
@string=@string+'v'+iterator +'inner join'... and so on
end
exec(@string)

thnak you

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-14 : 00:08:51

I'm trying to imagine a purpose for doing this.

CODO ERGO SUM

heze
Posting Yak Master

192 Posts

Posted - 2006-04-14 : 00:17:53

There is a need to build raw data sources to build pivot tables in excel and in the future in analysis services. I tried analysis services but was unsuccesful so as the deadline apporached I decided to do it by hand. They need to be able to drag and drop different columns which are both fine granular values and aggregates as well as calculated fiels. Im joining the prexisting views to calculate the original fields and I am createing new views to join these new views. With this I managed to produce exactly what they want with sql of relative low complexity. I tried to do it without dynamic sql but got too complex, of course, Im relatively new to sql so maybe I chose the wrong way,

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources