I have a column that has numbers and string , and I only want to get the numeric part, any easy way to do this? I used SELECT LEFT(TARGET_PCT, 2) 'TGT PCT' from employee
but also get the string? has to be in SELECT statement not in WHERE CLAUSE.
Pasi.
this is what I am doing now: LEFT(TARGET_PCT, PATINDEX('%[^0-9]%', TARGET_PCT ) - 1) and is working.
the filed has those characters and I am trying to remove them.
but you are mixing data types: string NO with string 10 when 10 is "numeric" but no NO is not. Then later on you are going to do some wonky stuff when using that to do calculations
case pct
when 'NO' then 1
else isnumeric(cast(pct as int)) then cast(pct as int) * the othernumricfield
end
exactly! now as far as I know there is no such thing as a blank number? (well at least not in this universe)
if it blank as you show it is a string or varchar! and you cant use that for calculations unless you do some Cirque Du Soleil gymnastics. do it right and correct now then you avoid issues later.
blank percentage or no percentage is really 1% am I correct? no that is not correct it is not 1%. there is no percentage period
no that is not what I am saying. I am saying don't mix data types in order to avoid case statements later on. that 10 is really a 10. it is 10 to our yes but you have it mixed in with a blank or NO string columns. that 10 is a string or varchar 10. I am saying dont do that. pick maybe a float and where there is no percentage maybe use a NULL