I get various material numbers like the following:


20890
138902
DEDKL2
XYFGAB

Now what I mean by alpha conversion is if the string a purely
numeric the left hand side must be padded with zeros if it is
alpabetic or alphanumeric the left had side must be left as it
is.

In the above case my field width is 10 so the above must be
padded after transformation as follows:

0000020890
0000138902
DEDKL2
XYFGAB

as you can see it as to see the number of characters in the
string and tab with that many zeros on the left hand side.

Also, given sometimes I get the data in the above with left
padded how do I remove all the left zeros and extract from the
first nonzero.

Please advise me how I can do this in DTS - VB Script sample code
or in T-SQL I have the unmodified data in a staging table if SQL
can generate this sequence please can you share the code.

Thanks
Karen

SELECT
CASE WHEN col NOT LIKE '%[^0-9]%'
THEN RIGHT(REPLICATE('0',10)+col,10)
ELSE REPLACE(RTRIM(LTRIM(REPLACE(col,'0',' '))),' ','0')
END
FROM SomeTable

--
David Portas
SQL Server MVP
--

Hi Karen,

I would prefer to build a userdefined function on the sql server side.
Something like


CREATE AplhaConvert( @cValue varchar(256) , @nLength integer )
RETURNS varchar(256)
AS
BEGIN
declare @cResult varchar(256)
if IsNumeric( @cValue ) set @cResult=dbo.StrZero( cast( @cValue as
integer ) , @nLength )
else set @cResult = @cValue
return( @cResult)
END

CREATE FUNCTION StrZero(@nNumber integer,@nLength integer)
RETURNS varchar(256)
AS
BEGIN
declare @cNumber varchar(256);
set @cNumber=RIGHT(REPLICATE('0',@nLength)+LTRIM(STR(@ nNumber)),@nLength);
return(@cNumber)
END

and then use the function in your select-statement

select ...., AlphaConvert (<column>,<length>) As Converted from .....

HTH
Meinhard


"Karen Middleton" <karenmiddleol (AT) yahoo (DOT) com> schrieb im Newsbeitrag
news:a5fd468a.0410192330.5e260094 (AT) posting (DOT) google.com...