Update a column with only the number from column

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

Update a column with only the number from column

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2012-04-11 : 09:03:19

Can someone help please. I have a table with a column named origResult which is a character field and some of the values in here (integer parts) should be extracted and put into the new column Results.

For example:

origResult
4.24 mmol/L (up to 5.00 ) See Guidelines

So I will be extracting only the 4.24 and storing that in the new column Results.

Can this be done?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-11 : 09:09:40

Do the numbers always appear at the beginning and are they always followed by a space?

Jim

Everyday I learn something that somebody else already knew

crazyco
Starting Member

30 Posts

Posted - 2012-04-11 : 09:13:53

No they don't always appear at the beginning or follow a space.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-11 : 09:18:36

Then how do you determine which number to extract? Is it just the first decimal number in the string, wherever it may be?

Jim

Everyday I learn something that somebody else already knew

crazyco
Starting Member

30 Posts

Posted - 2012-04-11 : 09:24:58

Yes it will just be the first number in the string, not necessarily a decimal number. Some results even have spaces before the first number. It's such a large table that I won't be able to update it manually.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-11 : 10:19:48

Someone smart will have to solve this one. With no set pattern in a string, it's hard to search a string for a pattern. You may need to do this in a UDF.

This works
declare @str varchar(50)
set @str = 'ggffr 4.24 mmol/L (up to 5.00 ) See Guidelines'

SELECT SUBSTRING(SUBSTRING(@str,PATINDEX('%[0-9]%',@str),50),1,PATINDEX('%[A-Z]%',SUBSTRING(@str,PATINDEX('%[0-9]%',@str),50))-1)

but not for this
'ggffr 4.24 6mmol/L (up to 5.00 ) See Guidelines'

Jim

Everyday I learn something that somebody else already knew

pupz09
Starting Member

3 Posts

Posted - 2012-04-11 : 10:32:56

help me
how can i put 2008-8-001 in the StudId where it is in a integer field

raffypa

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-11 : 10:36:00

quote:
help me
how can i put 2008-8-001 in the StudId where it is in a integer field

raffypa

NO CHANCE!! Don't hijack other posts please!

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 05:26:06

quote:
Originally posted by pupz09

help me
how can i put 2008-8-001 in the StudId where it is in a integer field

raffypa

Not possible because 2008-8-001 is not a valid integer value

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 05:27:51

You may be interested in this too
http://beyondrelational.com/modules/2/blogs/70/posts/10946/extract-only-numbers-from-a-string-part-3.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources