Strip numbers from nvarchar field

[kiwiCoder]

i have a field of type varchar
Example values: a1234, 123b,c963,1122,1235 etc
I need a way of seeing if there is a char(a-zA-Z)in each record and if so strip it out so I can have two derived fields for sorting on, One of type int and one char.
ie derived field 1 contains
a
b
c
derived field 2 contains
1234
123
1122
1235
I'm new to t-SQL and server programming, any help greatly appreciated.

 

[kiwiCoder]

update example
DECLARE @mystring varchar(15)
declare @tempstring varchar(1)
declare @charString varchar(15)
declare @intString varchar(15)
declare @itsAnumber int
set @mystring='a12345'
declare @i int
set @i=1
begin

while @i < Len(@mystring)+1
begin
select @tempstring = substring(@mystring,@i,1)
begin
if isnumeric(@tempstring) =1
--select @intString = @intString + @tempstring

set @intString = (@intString + @tempstring )
else
select @charString =( @tempstring)
end
select @i= @i+1
continue
end

print @intString + ' = intstring'
print @charString + ' = charstring'
print 'Number of loops = '
print @i
end

 

[donutman]

Try the solution suggested by ESquared in this thread, thread183-979386. Create one field at a time while you keep the original intact.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[kiwiCoder]

Thanks donutman, have created 2 UDFS each works to return either alpha or numeric, now the hard part. In order to be able to sort the numeric I need to either cast or convert the derived column as indicated by the example below, but it dont work

SELECT DISTINCT dbo.alphaonly(Run.Run)as alpharun ,

next line fails
cast((dbo.NumericOnly(Run.Run)as NumericRun) as int)

any ideas

If you love your job you never have to work again.

 

[donutman]

Why do you have to cast dbo.NumericOnly()? Doesn't the function return an integer? You may want to make sure the functions don't return a Null.
In any case you should alias the NumericOnly after you cast it not before.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]