How can i extract numeric values from varchar
-
hi all; I want to extract only numeric values from a column which have alphanumeric values like ABC12342M , AE2213Jk. Thanks Snehasish
-
You should create function which loops every character in string and check if is number or not.
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
-
hi; Many thanks for reply.It must work.But It will be lengthy process as per my application.So if you give any other soln,that will be helpful. Thanks snehasish
Well here is function which return only numbers from given string parameter.
CREATE FUNCTION [dbo].[ReturnNumber](@Parametri VARCHAR(8000)) returns varchar(8000) as begin declare @i as int set @i=0 declare @resultNumber as varchar(8000) while(@i<=len(@Parametri)) begin set @i=@i+1 if(select isnumeric(substring(@Parametri,@i,1)))=1 begin set @resultNumber =@resultNumber+substring(@Parametri,@i,1) end end return @resultNumer END
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
-
Well here is function which return only numbers from given string parameter.
CREATE FUNCTION [dbo].[ReturnNumber](@Parametri VARCHAR(8000)) returns varchar(8000) as begin declare @i as int set @i=0 declare @resultNumber as varchar(8000) while(@i<=len(@Parametri)) begin set @i=@i+1 if(select isnumeric(substring(@Parametri,@i,1)))=1 begin set @resultNumber =@resultNumber+substring(@Parametri,@i,1) end end return @resultNumer END
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.
hi; many thanks :) .I can not find any wrong in your function "ReturnNumber". but when i input a varchar, for example 'AA432k', it returns a null value. This is my query:
select dbo.ReturnNumber('AA432k')
or
select dbo.ReturnNumber(varcharcolumn) from test
what is my wrong? snehasish
-
hi; many thanks :) .I can not find any wrong in your function "ReturnNumber". but when i input a varchar, for example 'AA432k', it returns a null value. This is my query:
select dbo.ReturnNumber('AA432k')
or
select dbo.ReturnNumber(varcharcolumn) from test
what is my wrong? snehasish
After declaring of variable @resultNumber then initiliaze it with empty string.
. . . declare @resultNumer as varchar(8000) **set @resultNumer =''** . . .
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post.