CODEWITHSUNDEEP
mysqlfunction
Pall Arpad
Pall Arpad

Reputation: 1897

How to reference the current column value from a MySQL function

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

Answers (1)

Slava Rozhnev
Slava Rozhnev

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

Related Questions