| Author |
Topic |
|
Stanley Tan
Starting Member
25 Posts |
 Posted - 2002-08-10 : 06:42:50
|
| Is there a way to take only the numeric part of a field --> 21a = 21, 1234 ABC = 1234? |
|
|
Mazdak
Yak Posting Veteran
63 Posts |
Posted - 2002-08-10 : 06:59:08
|
| I think you can write UDF.Use ASCII( ) function to return each charachter and then check number returned,if it is one of numbers put it in another varable and at last return it. |
 |
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-08-10 : 08:34:15
|
| Isn't there a much easier way? I need to add it to the ORDER BY clause which is treating the field as a string rather than an integer because some fields have an extra letter e.g. 121a. Is this possible? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-10 : 08:46:56
|
| The real question is why do you have non-numeric characters in a column that you want to treat as numeric only? Are you cleaning up data imported from another source, or is this how it is supposed to be stored? And if it is, why do you want to treat it as numeric data when it isn't?Edited by - robvolk on 08/10/2002 08:47:29 |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-08-10 : 22:59:51
|
Thanks for the reply. It is supposed to be this way. Just imagine it as a catalog number field that also contains trailing characters. But while it is NOT numeric data, I still want to sort the data in numeric sequence. Right now, SQL server is sorting the results based on it's character values.quote:
The real question is why do you have non-numeric characters in a column that you want to treat as numeric only? Are you cleaning up data imported from another source, or is this how it is supposed to be stored? And if it is, why do you want to treat it as numeric data when it isn't?Edited by - robvolk on 08/10/2002 08:47:29
|
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-08-11 : 00:18:15
|
| Order by Right('000000' + FieldName, 6)should do the trick. It will order by the numeric part then the alpha part. I just picked 6 characters randomly. You'll need to use the actual width of your field.There isn't an easy way to just pull the numeric part out of a string that I know of.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-08-11 : 04:41:42
|
| If it's only non-negative integers at the start of the string, then it's not difficult.CAST(LEFT(col, PATINDEX('%[^0-9]%', col + 'x') - 1) AS int)This will treat strings with no leading digits as 0, since CAST('' AS int) = 0.Edited by - Arnold Fribble on 08/11/2002 04:46:46 |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-08-13 : 05:58:56
|
THANKS!!!!!!!quote:
If it's only non-negative integers at the start of the string, then it's not difficult.CAST(LEFT(col, PATINDEX('%[^0-9]%', col + 'x') - 1) AS int)This will treat strings with no leading digits as 0, since CAST('' AS int) = 0.Edited by - Arnold Fribble on 08/11/2002 04:46:46
|
|
|
|
|
Converting to Integer