Count characters after given symbol in oracle varchar column value
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?
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.
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.
链接地址: http://www.djcxy.com/p/92764.html