SQL query to replace character if exists within first 60 characters

Vinny Johnson
Vinny Johnson used Ask the Experts™
on
I have a long string and need to replace a "%" with a "-" but only if the "%" is within the first 60 characters.

Example input
recording-20181012_183357-7205756104-0-141850%252317199648923-0-HVDA-callhalf-15475983977%3A0_%5BcallingPartyNumber%5D.wav

example output
recording-20181012_183357-7205756104-0-141850-252317199648923-0-HVDA-callhalf-15475983977%3A0_%5BcallingPartyNumber%5D.wav
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
What database?

Oracle example:
select replace(substr(mycol,1,60),'%','-') || substr(mycol,61) from mydata;

Open in new window


Basically split the string into 2 sections, replace in the first section then concatenate them back together.

Author

Commented:
the database is mssql.  the % may be anywhere in the first 60 characters and not all records will need to have the % replaced. adjusting the syntax a little works!!

  replace(substring(mycol,1,60),'%','-')  + substring(mycol,61,61)

Thank you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial