I would like to write a query which using function concat / replace, change the title of a book into a jpg filename assigned to that book. In the book title I have some special characters.
- all of the special characters should be changed into -
- white space should be deleted
- If there are next to each other two special characters (like: ," in the example below) they merge into one character -
For instance: The book name: M. Mitchell,"Gone with the wind" the filename is: m-mitchell-gonewiththewind.jpg
Query:
select *
REPLACE (REPLACE (REPLACE (REPLACE (REPLACE
' ', ''), /*white space - ok */
'„', '-'), /*quotation - ok */
'”', '-'), /*quotation - ok */
' \' ', '-'), /* apostrophe - ok */
' " ', '-') /*here is the problem*/
from ...
Problems:
- AD 1
- " changes into "
- \" changes also into "
- AD 3 Should I add new query into another column? Or use IF?
For .. The book name:M. Mitchell,"Gone with the wind"
the filename is:m-mitchell-gonewiththewind.jpg
Your query did not fail on the example. Your example did not have characters
'„'
, '”'
and sequence of characters as ' " '
. And hence query did not result as you expected.
Change the query as below:
select
t,
replace(
replace(
trim(
replace(
replace(
replace(
replace(
replace( t, '.', ' ' ), -- replaces all dots
',', ' ' ), -- replaces all commas
'„', ' ' ), -- replaces all „s
'”', ' ' ), -- replaces all ”s
'"', ' ' ) -- replaces all "s
), -- removes trailing spaces
' ', '-' ), -- replaces all space chars
'--', '-' ) -- replaces all repeated hyphen -s
as title
from ( select 'M. Mitchell,"Gone with the wind"' t ) titles ;
Demo on SQL Fiddle
0 comments:
Post a Comment