3

How would I go about counting the characters after a certain character. I'm new to Oracle, and I've learned quite a bit however I'm stumped at this point. I found a couple functions that will get you a substring and I found a function that will give you the length of a string. I am examining an email address, myemail@thedomain.com. I want to check the length after the '.' in the email.

SELECT email
FROM user_table
WHERE length(substr(email, /*what values*/, /*to put here*/))

I don't know if it's actually possible to find the location of the final '.' in the email string?

Sebas
  • 21,192
  • 9
  • 55
  • 109
Trim
  • 375
  • 1
  • 5
  • 14
  • after the '.' or after the '@'? – Sebas Jun 11 '12 at 20:23
  • I apologize, I want to count after the '.' – Trim Jun 11 '12 at 20:27
  • ok, actually I'm wondering whether you're checking for the length for some of your own reasons, or you're actually checking the email validity? – Sebas Jun 11 '12 at 20:29
  • I'm checking to confirm the TLD (top level domain) is 3 characters and under. – Trim Jun 11 '12 at 20:39
  • Bad idea, they could be up to 6 characters :-) – Sebas Jun 11 '12 at 20:43
  • Haha I know they can be that long, e.g. .museum, but I want to make sure everything is 4 or under (: – Trim Jun 11 '12 at 20:45
  • @Trim, as I just commented under Sebas' answer do not use regular expressions to validate e-mail addresses. On top of the answers in those questions non-latin TLDs now exist and anyone can make up their own. – Ben Jun 11 '12 at 21:20

2 Answers2

3

I'm not sure I would use substr. You can try something like this :

select length('abcd@efgh.123.4567') - instr('abcd@efgh.123.4567', '.', -1) from dual

Using instr(..,..,-1) searches backwards from the last character to find the position.

Mike Park
  • 10,845
  • 2
  • 34
  • 50
  • This works until email addresses such as my.email@thedomain.com appear. It only looks at the first '.', not the final one. Any way around this? – Trim Jun 11 '12 at 20:49
2

Since you're doing checks, I suggest you validate the format with regular expressions using REGEXP_INSTR. For instance, an email validation I found on this site is REGEXP_INSTR(email, '\w+@\w+(\.\w+)+') > 0

I didn't check it myself, but it looks quite ok.

Cheers.

Sebas
  • 21,192
  • 9
  • 55
  • 109
  • I might have upvoted for the regexes. However, I cannot up-vote anyone who suggests using a [regular expression to validate an e-mail address](http://ex-parrot.com/~pdw/Mail-RFC822-Address.html); it is [technically impossible](http://stackoverflow.com/questions/156430/regexp-recognition-of-email-address-hard). – Ben Jun 11 '12 at 21:18
  • 1
    I know, I never recommended that! The user wants to do some checks, I'm just helping him doing his checks... :-) – Sebas Jun 12 '12 at 00:33