How to remove a leading character from numeric string? SQL-CodePudding

I have a list of tournament results from PGA tour data and would like to remove the "T" from the beginning of the finish column strings where applicable, so that I can get an average number. The string lengths are variable and also contain "CUT" in some rows. Is there a way to remove the "T"?

I have used...

WHERE Finish not like "CUT"

to remove "CUT" values

and have used various functions with no success to remove the "T". Any help would be greatly appreciated! Thanks

Showing variable string lengths in Finish column

EDIT:

This is what I have so far, which works perfectly to aggregate averages and group by player in a single row as desired.

SELECT

DISTINCT(player),
  ROUND(AVG(CAST(sg_putt as numeric)),2) as avg_sg_putt, 
  ROUND(AVG(CAST(sg_arg as numeric)),2) as avg_sg_arg, 
  ROUND(AVG(CAST(sg_app as numeric)),2) as avg_sg_app, 
  ROUND(AVG(CAST(sg_ott as numeric)),2) as avg_sg_ott, 
  ROUND(AVG(CAST(sg_t2g as numeric)),2) as avg_sg_t2g, 
  ROUND(AVG(CAST(sg_total as numeric)),2) as avg_sg_total,
  SUM(made_cut) as cuts_made,
  COUNT(DISTINCT(tournament_id)) as total_played,

FROM

`pga_stats_2015_2022.stats`

WHERE 

season >= 2017 AND
sg_putt not like "NA" AND
sg_arg not like "NA" AND
sg_app not like "NA" AND
sg_ott not like "NA" AND
sg_t2g not like "NA" AND
sg_total not like "NA"


GROUP BY player
HAVING total_played > 50
ORDER BY(avg_sg_total) DESC

CodePudding user response：

From the documentation here, it seems you want REPLACE:

REPLACE(original_value, from_value, to_value)

So for instance,

SELECT REPLACE(Finish,'T','') as Finish
FROM yourTable
WHERE Finish <> 'CUT'

EDIT:

Looking at your full query, I suspect you want to add:

ROUND(AVG(CAST(REPLACE(Finish,'T','') as numeric)),2) as avg_Finish

to your SELECT.

Then add:

WHERE Finish <> "CUT"

to your WHERE

CodePudding user response：

Perhaps ltrim does the trick

select ltrim(finish,'T') --might want to cast to int before calculating avg
from..
where..

Note that ltrim removes all occurrences from the left so it'll remove T from both T6 and TT6 for example