Extract only Numeric not string

Hi ,

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.


why are mixing data types?
10% ICP is 10

That will surely come to bite you again.

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

this is what I am getting now: no string, its blank now, which is what I want.


so thinking ahead, lets say you will use that new cleaned up column for some calculation what will you do?
Future proofing it, if you are interested

you are correct.. will have to modify the code.

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

hold on just doing a back flip to see if that's correct? so your saying to use a CASE?

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

OK Thanks! can you show how to do this with select?

what is the end game for this select? a report a ui or dumping the data into another table? a view?

its for UI report not any other table.