Checking for Numeric Data 2

[SpaceMonkey4969]

Hello,

My problem is:

I have a nvarchar field that contains numerics and characters. an example would be 30mg. I need some sort of routine or function i can use to store the 30 in a variable that is of type Numeric.

Thx in advnace

 

[ClaireHCM]

Say your table look like this!!

create table A(id int,col1 nvarchar(20))
insert into a select 1,'30mg'
insert into a select 2,'100mg'
insert into a select 3,'293mg'
insert into a select 4,'93mg'
insert into a select 5,'180mg'
insert into a select 6,'2mg'
insert into a select 7,'60mg'

--------------------------------------------------
How to get the numeric part out of col

select convert(decimal(5,2),left(col1,len(col1)-2)) from a
or
select convert(int,left(col1,len(col1)-2)) from a

Is this what you want??

 

[SpaceMonkey4969]

ok, I Guess i Should give more information

the data can range from anything such as 4g or 4 g.. to something like 100mg/ml

 

[ClaireHCM]

This should solve the trick!


select replace(col1,
(replace(replace(replace
(replace(replace(replace
(replace(replace(replace
(replace(col1,char(48),''),char(49),'')
,char(50),'')
,char(51),'')
,char(52),'')
,char(53),'')
,char(54),'')
,char(55),'')
,char(56),'')
,char(57),''))
,'') from a

 

[karluk]

You should search for the first nonnumeric character and then do the conversion that ClaireHsu suggests. Something like the following is probably close to what you need.

select convert(int,left(col1,patindex('%[^0-9]%', col1)-1)) from a
go

 

[ClaireHCM]

soory I found a better way!(C is my basis that's why It's easy for me to think ascii and char)

select replace(col1,
(replace(replace(replace
(replace(replace(replace
(replace(replace(replace
(replace(col1,1,''),2,''),3,''),4,''),5,''),6,''),7,''),8,''),9,'')
,0,'')),'') from a

 

[tlbroadbent]

If you are using SQL Server 2000, you can create a UDF to extract the numeric value.

Create Function dbo.udfExtractNumber
(@InputString varchar(12))
Returns Numeric(18,6)
As

Begin
Declare @CharPos int, @RetValue float, @Char1 char(1)

Set @CharPos=1

While @CharPos <= len(rtrim(@InputString))
Begin
Set @char1 = Substring(@InputString, @CharPos, 1)

If @char1 Like '[0-9]' Or @char1 = '.'
Begin
Set @CharPos=@CharPos+1
End
Else
Begin
Set @RetValue=Cast(Left(@InputString, @CharPos-1) As Numeric(18,6))
Set @CharPos=len(rtrim(@InputString))+1
End
End

Return @RetValue

End
Go

Use the function like this.

Select
character_value,
num_value=dbo.udfExtractNumber(character_value)
From YourTable


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[ClaireHCM]

karluk's way is cool!
Star for you!