Retrieving Soundex Codes from a Data Column in mysql-CodePudding

is it possible to make such a query in SQL: there is a column with names, let's say FirstName, you need to get the soundex code for each name in the column and write these codes into the FirstNamesdx column?

CodePudding user response：

Are you trying something like this:

CREATE TABLE test_tbl(
             first_name VARCHAR(50),
             FirstNamesdx VARCHAR(50)
                     );

insert into test_tbl(first_name) values ('Earbuds'),
                                        ('Phone'),
                                        ('Charger'),
                                        ('Data Cable'),
                                        ('Speakers');

Then you can use an update with the same table to get the needed values:

update test_tbl a
inner join 
( select first_name, SOUNDEX(first_name) as soundex_first_name 
  from test_tbl
) as b
on a.first_name=b.first_name
set a.FirstNamesdx=b.soundex_first_name;

You have an easy way:

update test_tbl
set FirstNamesdx= SOUNDEX(first_name);

Demo: