|
Replies:
3
-
Pages:
1
-
Last Post:
Nov 15, 2011 2:20 AM
by: Guy_M
|
|
|
Posts:
2
Registered:
11/8/11
|
|
|
|
User Defined Functions
Posted:
Nov 8, 2011 11:54 AM
|
|
|
Hope someone can point me in the right direction for this.
I need to create a user-defined function which in this case will compress out all non-alphanumeric characters and return that string. I want to apply it against a column in a table and put the results in another column.
There is some sketchy, incomplete info on how to do this, but I end up wasting a lot of time using trial and error to try and get this to work.
Documentation and examples would be really helpful.
Thanks
|
|
|
Posts:
1
Registered:
11/9/11
|
|
|
|
Re: User Defined Functions
Posted:
Nov 9, 2011 4:22 AM
in response to: Turtle-to-Toad
|
 |
Answered |
|
|
when you say: "compress out all non-alphanumeric characters and return that string"
you mean remove non numerical characters?
I hope this can help you:
DROP FUNCTION IF EXISTS f_strip_non_alpha; CREATE FUNCTION f_strip_non_alpha( _dirty_string varchar40)) RETURNS varchar(40) CHARSET latin1 READS SQL DATA DETERMINISTIC BEGIN DECLARE _length int; DECLARE _position int; DECLARE _current_char varchar(1); DECLARE _clean_string varchar(40); SET _clean_string = ''; SET _length = LENGTH(_dirty_string); SET _position = 1; WHILE _position <= _length DO SET _current_char = SUBSTRING(_dirty_string, _position, 1); IF _current_char REGEXP '[0123456789]' THEN SET _clean_string = CONCAT(_clean_string, _current_char); END IF; SET _position = _position + 1; END WHILE; RETURN CONCAT('', _clean_string); END;
|
|
|
Posts:
2
Registered:
11/8/11
|
|
|
|
Re: User Defined Functions
Posted:
Nov 11, 2011 12:46 PM
in response to: Omar Rodriguez
|
|
|
Thanks Omar, I was hoping to use REGEXP eventually .. you beat me to it.
Cheers!
|
|
|
Posts:
26
Registered:
2/16/11
|
|
|
|
Re: User Defined Functions
Posted:
Nov 15, 2011 2:20 AM
in response to: Omar Rodriguez
|
|
|
Hi,
Please, could you explain why do you use an underscore char '_' as first char of all your var name ?
Thank you for your anwser.
Please apologise for my poor english.
Guy
|
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
