Reputation: 1897
I want to create a function which replaces middle characters with '.' (dots) in column values (ex: 'something' => 's.......g')
The problem is I don't know how to reference the current column value inside the function body, I'm looking for something like the this
keyword in JavaScript
DELIMITER$$
CREATE FUNCTION middleDots()
RETURNS --current datatype
BEGIN
--how to reference the current column value?
RETURN --current modified colum value
END$$
DELIMITER;
So I could use the function like this
SELECT middleDots(col1) AS col1, middleDots(col2) AS col2 FROM someTable;
I can write a query which does this just fine but using a simple function will be much better
Query :
mysql> SELECT col1,
-> INSERT(col1, 2, CHARACTER_LENGTH(col1) - 2, REPEAT('.', CHARACTER_LENGTH
(col1) - 2)) AS col1,
-> INSERT(col2, 2, CHARACTER_LENGTH(col2) - 2, REPEAT('.', CHARACTER_LENGTH(col2) - 2)) AS col2
-> FROM someTable;
Upvotes: 0
Views: 320
Reputation: 10163
You can create function by next way:
DROP FUNCTION IF EXISTS middleDots;
DELIMITER $$
CREATE FUNCTION middleDots(`input` VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
DECLARE `output` varchar(255) DEFAULT '';
SELECT CONCAT(
LEFT(`input`, 1),
REPEAT('.', CHARACTER_LENGTH(`input`) - 2),
RIGHT(`input`, 1)) INTO `output`;
RETURN `output`;
END$$
DELIMITER ;
In this code function input
parameter will be referenced with column.
Upvotes: 1