CODEWITHSUNDEEP
sqlsql-serverregext-sql
moritzg
moritzg

Reputation: 4396

SQL: 2 same vowels regex

I need a regex for 2 same vowels in a varchar.

Like 'linEbrEak' (but only 2 not more and not less)

I thought of something like:

'%[aeiouAEIOU]%[aeiouAEIOU]%'

But that gives me any 2 vowels. I want two of the same kind.

I'm using SQL Server 2008.

Is this even possible in Regex? Thanks very much!

More examples:

LeIpzIg
LOndOn

Edit: It is ok if it's not possible (too difficult maybe) in regex, I am happy with other solutions too, but they should be easy to read, fast and short.

Upvotes: 0

Views: 878

Answers (2)

Mark
Mark

Reputation: 1088

This isn't pretty or short but it is simple.

SELECT word
FROM tabl
WHERE
  -- assuming case sensitive based on your example
  (word LIKE '%[Aa]%[Aa]%' AND word NOT LIKE '%[Aa]%[Aa]%[Aa]%')
  OR
  (word LIKE '%[Ee]%[Ee]%' AND word NOT LIKE '%[Ee]%[Ee]%[Ee]%')
  OR
  (word LIKE '%[Ii]%[Ii]%' AND word NOT LIKE '%[Ii]%[Ii]%[Ii]%')
  OR
  (word LIKE '%[Oo]%[Oo]%' AND word NOT LIKE '%[Oo]%[Oo]%[Oo]%')
  OR
  (word LIKE '%[Uu]%[Uu]%' AND word NOT LIKE '%[Uu]%[Uu]%[Uu]%')

It occurs to me that you didn't specify what to do for a place that has two of one vowel and three of another. Does that qualify? If not (Say Alaska StatE PEak Park was bad even though it has exactly 2 E's in it) then you might want this instead:

SELECT word 
FROM tabl 
WHERE
  -- assuming case sensitive based on your example
  ( word LIKE '%[Aa]%[Aa]%'
    OR word LIKE '%[Ee]%[Ee]%'
    OR word LIKE '%[Ii]%[Ii]%'
    OR word LIKE '%[Oo]%[Oo]%'
    OR word LIKE '%[Uu]%[Uu]%' 
  )
  AND word NOT LIKE '%[Aa]%[Aa]%[Aa]%'
  AND word NOT LIKE '%[Ee]%[Ee]%[Ee]%'
  AND word NOT LIKE '%[Ii]%[Ii]%[Ii]%'
  AND word NOT LIKE '%[Oo]%[Oo]%[Oo]%'
  AND word NOT LIKE '%[Uu]%[Uu]%[Uu]%'

Upvotes: 4

EricZ
EricZ

Reputation: 6205

One possible solution

DECLARE @txt VARCHAR(100) = 'LeIpzIg'

DECLARE @vowels TABLE ( letter CHAR(1))
INSERT INTO @vowels VALUES ('a'),('e'),('i'),('o'),('u') 

;WITH cte AS (
    SELECT letter , LEN(@txt) - LEN(REPLACE(LOWER(@txt),letter,'')) as number_of_vowel
    FROM @vowels
    WHERE LOWER(@txt) LIKE '%'+ letter +'%'
) 
SELECT letter
FROM cte 
WHERE number_of_vowel >= 2

Upvotes: 0

Related Questions