Concatenating two fields and replacing special/spaces characters

Question

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), '[$!&*_;:@#+\'=%^,<.>/?|~-]', '')

fname and lname can contain spaces but when we concatinate both and display in fullname that time it shouldn't contain spaces, specialcharacters etc... @ just for example it might not be special character but except alphabets and number need to remove all other. — Narayan bhat, Apr 25 '23 at 12:07
"it didn't work" - what happens instead? What else did you try to reoslve the problem? — Nico Haase, Apr 25 '23 at 12:08
Why your first option is not supposed to work? Looks like working correctly when excluding the space inside the non required characters. — lemon, Apr 25 '23 at 12:09
`SELECT concat( REGEXP_REPLACE(fname, '[^0-9a-zA-Z]', ''), REGEXP_REPLACE(lname, '[^0-9a-zA-Z]', '') ) as fullname;` — RiggsFolly, Apr 25 '23 at 12:11
Or if you want a space between the first and last name `SELECT concat( REGEXP_REPLACE(@fname, '[^0-9a-zA-Z]', ''), ' ', REGEXP_REPLACE(@lname, '[^0-9a-zA-Z]', '') ) as fullname;` — RiggsFolly, Apr 25 '23 at 12:13
May be better to execute REGEXP_REPLACE once only, by swapping the order of the operations. — lemon, Apr 25 '23 at 12:20
@lemon That would work if you dont want a space between firstname and lastname :) — RiggsFolly, Apr 25 '23 at 12:23

score 1 · Answer 1 · edited Apr 25 '23 at 12:32

1

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

edited Apr 25 '23 at 12:32

RiggsFolly

93,638
21
103
149

answered Apr 25 '23 at 12:12

DataWrangler

1,804
17
32

Get this error : Error Code: 1370. execute command denied to user 'xyz'@'%' for routine 'test.REGEXP_REPLACE', able to do all other SELECT but when I use REGEXP_REPLACE getting error – Narayan bhat Apr 25 '23 at 12:15
Looks like a permission issue on the `EXECUTE` command, instead of calling the execute. Are you able to use the `SELECT` and get the results as expected ? – DataWrangler Apr 25 '23 at 12:22
I am using SELECT only still I get the denied error – Narayan bhat Apr 25 '23 at 12:35
Check this post https://stackoverflow.com/questions/6434573/mysql-execute-command-denied-to-user-localhost-for-routine-error – DataWrangler Apr 25 '23 at 13:00

score 1 · Answer 2 · answered Apr 25 '23 at 12:28

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.

Concatenating two fields and replacing special/spaces characters

2 Answers2