[RESOLVED] Parse non-numeric characters

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

[RESOLVED] Parse non-numeric characters

Author

Topic

obsoletedude
Starting Member

31 Posts

Posted - 2007-03-13 : 11:45:15

I'm attempting to move data from a text field to a numeric field, but I need to be able to remove any non-numeric characters in that field first, but I do not want to lose the numeric data.

In the code below, the Value table is the table that will be converted.

INSERT INTO IntegerValues (FK_ReferenceID,FK_ReferenceType,FK_FieldID,FieldProperty,Value,CreationDate,CreatedBy,Revision)
SELECT FK_ReferenceID,FK_ReferenceType,FK_FieldID,FieldProperty,Value,CreationDate,CreatedBy,Revision
FROM StringValues WHERE FK_FieldID in (select id from ##tmpFields)

Any ideas on how I may do that?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-13 : 13:50:57

"...but I do not want to lose the numeric data."...where do you propose this non-numeric data is put?

using a "AND ISNUMERIC(value)" clause will get you some of the way there....AS LONG as there are no scientific numbers in the Value field.

obsoletedude
Starting Member

31 Posts

Posted - 2007-03-13 : 14:15:41

To be honest, any non-numeric characters can be discarded. In the old Value, there may something like 1,234 SF. All I need is the 1234 and everything else can be discarded.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-13 : 14:43:25

create a simple User defined function to do the job ..... something like this should work fine:


create function NumbersOnly(@txt varchar(1000))
returns int
as
begin
    declare @i int
    declare @ret varchar(100)
   
    select @i = 1, @ret = ''

    while (@i <= len(@txt))
        select @ret = @ret + case when substring(@txt,@i,1) like '[0-9]' 
                                  then substring(@txt,@i,1) else '' 
                             end,
               @i = @i + 1

    return case when @ret !='' then convert(int, @ret) else Null end
end

then, after creating the function, you'd say:

insert into integervalues (....)
select ..., dbo.NumbersOnly(Value), ....
from Yourtable

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-13 : 15:40:08

Maybe some of these?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

Peter Larsson
Helsingborg, Sweden

obsoletedude
Starting Member

31 Posts

Posted - 2007-03-13 : 16:32:11

Thanks, I will try these tonight and let you know. I'm looking forard to seeing if this works.

obsoletedude
Starting Member

31 Posts

Posted - 2007-04-05 : 15:45:56

Sorry it took so long to write back. Finally had a chance to run this script. The script runs fine, but when I access the information, I'm getting: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." I removed all rows that contained NULLS and empty data prior to running this script and same message. Looking at the table, there are no NULLs, nor are any non-nemeric characters (you guys rock), but I have modified the function to:

create function NumbersOnly(@txt varchar(1000))
returns int
as
begin
declare @i decimal(18,6)
declare @ret varchar(100)

select @i = 1, @ret = ''

while (@i <= len(@txt))
select @ret = @ret + case when substring(@txt,@i,1) like '[0-9]'
then substring(@txt,@i,1) else ''
end,
@i = @i + 1

return case when @ret !='' then convert(decimal(18,6), @ret) else Null end
end

The table I am placing this information in is set to decimal(18,6). Should this work?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-05 : 16:51:44

It should, but you need to change this:

create function NumbersOnly(@txt varchar(1000))
returns ~~int~~ decimal(18,6)

Also: don't ask us -- test it yourself! (and by "test", I do NOT mean "run it on your live data and see what happens!")

- Jeff
http://weblogs.sqlteam.com/JeffS

obsoletedude
Starting Member

31 Posts

Posted - 2007-04-05 : 16:55:59

Thanks, Jeff. I should have proofread that script first, huh? I'll "test" it out, and don't worry, nothing gets done on production until everything works great. (I like my job, and this SQL stuff is an added bonus that they are letting me play with, until production databases disappear!)

obsoletedude
Starting Member

31 Posts

Posted - 2007-04-05 : 17:05:32

Argument data type decimal is invalid for argument 2 of substring function.

Pointing to:
select @ret = @ret + case when substring(@txt,@i,1) like '[0-9]'

I'm assuming I need to specify a decimal (.) How would I do that on that line?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-05 : 23:44:08

Also refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56713

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-06 : 08:18:18

I just noticed you had this:

>> declare @i decimal(18,6)

That needs to stay as an int, as I originally had it.. that has nothing to do with the return value.

>> declare @i ~~decimal(18,6)~~ int

By the way -- if you are returning a decimal (18,6) then your specifications make no sense. Why are you returning something with 6 decimal places when your parsing rules don't allow any numbers with decimals?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources