0

Trying to remove the extra space in a string within words like

1. AMERICAN RESTAUR               ANT --->   AMERICAN RESTAURANT
2. SCHOOL OF          BUSINESS   ---->  SCHOOL OF BUSINESS
3. COMMUNITY SE     RVICE  ----> COMMUNITY SERVICE
4. THE RINK-LAKEWOO           D ----> THE RINK-LAKEWOOD

I have used following link to remove MySQL : how to remove double or more spaces from a string?

and i could get till this using one of the function from above link

1. AMERICAN RESTAUR               ANT --->   AMERICAN RESTAURANT
2. SCHOOL OF          BUSINESS   ---->  SCHOOL OF BUSINESS
3. COMMUNITY SE     RVICE  ----> COMMUNITY SE RVICE
4. THE RINK-LAKEWOO           D ----> THE RINK-LAKEWOO D

Can you please help me out.

Community
  • 1
  • 1
Sql_Learner
  • 53
  • 1
  • 1
  • 7

3 Answers3

1

This worked for me 

trim(preg_replace('/[\s\t\n\r\s]+/', ' ', $text_to_clean_up))

You can replace or remvoe the excess space and lines in the string.

Tunaki
  • 132,869
  • 46
  • 340
  • 423
Bala Murali
  • 11
  • 1
0

I'm not sure if you can do this effectively in a pure SQL solution. The general approach I would use is to perform a regular expression search to find non-whitespace characters, followed by 2 or more spaces, followed by non-whitespace characters. In your third example the results would be three regex groups...

Group 1 - "SE"
Group 2 - "     "
Group 3 - "RVICE"

This is the part where I think you need to step outside of SQL. If you used a spell-check API you could apply the following logic...

If Group 1 isn't in the dictionary AND Group 3 isn't in the dictionary, but the concatenation of Group 1 and 3 IS in the dictionary then remove the spaces. So continuing our example...

"SE" is not in the dictionary. "RVICE" is not in the dictionary. "SERVICE" is in the dictionary so remove the spaces between.

Even this approach isn't perfect. If you take an example like "OVER FLOW" both are going to be in the dictionary and the spaces will not be removed.

Not a solution per se', but hopefully some useful food for thought.

dazedandconfused
  • 3,131
  • 1
  • 18
  • 29
0

Try below code : I hope which will help :

select replace(replace(replace(replace(replace(replace(text1,'  ','~'),' ','%'),'~~',''),'~%',''),'%',' '),'~',' ') from temp1

refer below link on which i tried your example : http://sqlfiddle.com/#!2/b51833/1

Hemant Patel
  • 184
  • 6