| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-03-13 : 17:02:02
|
| I have a column (bondinfo) in my warrantdata table that is a mix of alpha and numeric. I would like to select just the numerical values from that column. I know i need to use ISNUMERIC, but not quite sure how. The column is a text column.Select Bondinfo(ISNUMERIC) from warrantdataAint happenin'thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-03-13 : 17:09:32
|
| I got the following to "work", as i had to cast it:SELECT ISNUMERIC(Cast (Bondinfo AS VARCHAR(MAX))) FROM WarrantData wdbut the results it's providing are clearly not "true"Is there any way to use a LIKE, PATINDEX, or CHARINDEX to pull the numbers from that column?Yes, i refer to the books online (and others) when i'm feeling ambitious. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-03-13 : 17:19:22
|
| well in that case, it works splendidly. interestingly (and annoyingly) it's also pulling the '$' from that string along with the numeric value. I'll chew on how to lose that. if i can't figure it out, i'll be back.thanks again! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-03-14 : 09:11:41
|
Yeah, i got that far. My first attempt, hosed that column completely wiping out all data in that column:UPDATE warrantdata SET bondinfo = REPLACE('$','$','')I originally thought that script should have looked like this:UPDATE warrantdata SET bondinfo = REPLACE('bondinfo','$','')That one wouldn't even fire off, i figured because of the nature of the defecated text column, which is why i used the first version of that ('$','$',''), so i figure i need to recast it in the replace portion, but that one is arguing about the syntax around the REPLACE:UPDATE warrantdata SET bondinfo = (cast(Bondinfo as varchar(max)))REPLACE(Bondinfo,'$','')did i also mention that the sql books online make me.........sad? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-03-14 : 12:55:57
|
| The only anomaly i'm seeing are the "$" symbols remaining which i'm trying to get rid of with the 'replace' in the same statement. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-03-14 : 15:10:18
|
| Sorry, i missed it the first time around. Yes, as i see more of this old data cropping up as TEXT, i'm going to start casting and converting that data first off on each project. Thanks for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
isnumeric