Question 58189 in Mysql Question posted in
The official MySQL documentation can be found here.

Concatenating two fields and replacing special/spaces characters – Mysql

I have sample data that looks like the following:

fname lname
x abc jkn
test test gth
yg-txs gb@y

Need to write simple query to

  • remove all special character and spaces,
  • concat fname and lname
  • put it in fullname column, whereas values contained in this field must be without spaces and special characters.

Need optimized query because we have millions of data.

"Expected Output":

fname lname fullname
x abc jkn xabcjkn
test test gth testtestgth
yg-txs gb@y ygtxsgby

I have tried below it didn’t work:

  • REGEXP_REPLACE({column}, '[^0-9a-zA-Z ]', '')
  • (REPLACE(CONCAT(fname,lname), '[$!&*_;:@#+'=%^,<.>/?|~-]', '')
Tags:

2

Answers


  1. 0

    You just need to remove the space on the REGEX rest all looks good

    Schema (MySQL v8.0)

    CREATE TABLE tableName 
(
    fname   VARCHAR(512),
    lname   VARCHAR(512)
);

INSERT INTO tableName (fname, lname) VALUES ('x abc', 'jkn');
INSERT INTO tableName (fname, lname) VALUES ('test', 'test gth');
INSERT INTO tableName (fname, lname) VALUES ('yg-txs', 'gb@y');

    Query #1

    SELECT  REGEXP_REPLACE(fname, '[^0-9a-zA-Z ]', '')fname, 
        REGEXP_REPLACE(lname, '[^0-9a-zA-Z ]', '')lname, 
        CONCAT(REGEXP_REPLACE(fname, '[^0-9a-zA-Z]', ''),
               REGEXP_REPLACE(lname, '[^0-9a-zA-Z]', '')
        ) fullname 
FROM tableName;
    fname lname fullname
    x abc jkn xabcjkn
    test test gth testtestgth
    ygtxs gby ygtxsgby
    Login or Signup to reply.
  2. 0

    As already pointed in the comments section, your issue is related to the space existing inside your regex. Additionally, employing the REGEXP_REPLACE only once may give you a margin of efficiency improvement, if your requirement is not having a space between first and last name.

    SELECT fname,
       lname,
       REGEXP_REPLACE(CONCAT(fname,lname), '[^0-9a-zA-Z]', '') AS fullname
FROM tab

    Output:

    fname lname fullname
    x abc jkn xabcjkn
    test test gth testtestgth
    yg-txs gb@y ygtxsgby

    Check the demo here.

    Note: If you can’t use the REGEXP_REPLACE command, you should ask for permissions to your provider. This is relevant as long as you’re working on millions of data and require an optimized solution.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search