CODEWITHSUNDEEP
sql
unforgiven
unforgiven

Reputation: 155

SQL - find rows with values that has 2 spaces or more in between

I have SQL table with name and surname. Surname is in own column. The problem is with users with two surnames, because sometimes they add more than one space between surnames and then I have to find and fix them manualy.

How to find these surnames with more than one space in between?

Upvotes: 5

Views: 14033

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

If you want to find records which have more than one space then you can use the following trick:

SELECT surname
FROM yourTable
WHERE LENGTH(REPLACE(surname, ' ', '')) < LENGTH(surname) - 1

This query will detect two or more spaces in the surname column. If you want to also do an UPDATE this is possible, but it would be fairly database-specific, and you did not specify your database as of the time I wrote this answer.

Upvotes: 6

jarlh
jarlh

Reputation: 44746

First remove those extra spaces. Then add a constraint that makes sure it doesn't happen again:

alter table tablename add constraint surname_verify check (surname not like '%  %')

(Or, even better, have a trigger making sure the surnames are properly spaced, cased etc.)

How to remove extra spaces? Depends on the dbms.

You can perhaps do something like:

update tablename set surname = replace(surname, '  ', ' ')
where surname like '%  %'

The where clause isn't needed, but makes the transaction much smaller.

(Iterate to get rid of triple or more spaces.) Or use regexp_replace.

Upvotes: 3

Văn Tuấn Phạm
Văn Tuấn Phạm

Reputation: 659

Even tidier:

select string = replace(replace(replace(' select   single       spaces',' ','<>'),'><',''),'<>',' ')

Output:

select single spaces

Upvotes: 0

Related Questions