Integrating Replace Function in Access 2016 SQL Query

Question

I have the following SQL query and want the replace all occurrences of the phrase "Vitality Blast" in the League field with "Twenty20 Cup (England)". To do this I think I need to use the Replace function but I do not know how to integrate it.

Can anyone point me in the right direction?

Thanks

SELECT BallByBall.matchId, BallByBall.inningsNo, BallByBall.ballNumber, BallByBall.ballRank, BallByBall.bowler, BallByBall.batsman, BallByBall.runs, BallByBall.byes, BallByBall.legByes, BallByBall.wides, BallByBall.noBalls, BallByBall.wicket, Lineups.bowlingStyle, Matches.League, Matches.convDate, ScorecardBatting.howDismissed, (SELECT COUNT(T2.ballRank) + 1
FROM BallByBall T2
WHERE T2.matchId = BallByBall.matchId
AND T2.batsman = BallByBall.batsman
AND T2.ballRank < BallByBall.ballRank
) AS Rank
FROM ScorecardBatting RIGHT JOIN (Matches RIGHT JOIN (Lineups RIGHT JOIN BallByBall ON (Lineups.matchId = BallByBall.matchId) AND (Lineups.playerId = BallByBall.bowler)) ON Matches.matchId = BallByBall.matchId) ON (ScorecardBatting.matchId = BallByBall.matchId) AND (ScorecardBatting.batsmanId = BallByBall.batsman)
ORDER BY BallByBall.matchId, BallByBall.inningsNo, BallByBall.ballRank;

Hi Maciej, it's text from a csv file. Is that what you mean? — rob
– rob, Commented Sep 10, 2020 at 13:05
Yeah. And i'm also interested what you mean by saying: "replace all occurrences of the phrase "Vitality Blast" in the League field with "Twenty20 Cup (England)"". Does "Visibilit Blast" exists in League field more then once per row? — Maciej Los
– Maciej Los, Commented Sep 10, 2020 at 13:15

Maciej Los · Accepted Answer · 2020-09-10 13:57:08Z

1

In reference to our discussion in comments, you have to use UPDATE statement.

UPDATE YourTableName SET League = 'Twenty20 Cup (England)'
WHERE League = 'Vitality Blast';

For further details, please see: UPDATE statement (Microsoft Access SQL)

answered Sep 10, 2020 at 13:57

Maciej Los

8,6311 gold badge22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rob Over a year ago

Hi Maciej, thanks. Can this be integrated into the existing code?

Maciej Los Over a year ago

Not sure, what you mean... Updating data is used to change its "content". Update command can NOT be used to select data. You have to be more clear. Please, provide sample data and expected result (improve your question).

Collectives™ on Stack Overflow

Integrating Replace Function in Access 2016 SQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related