Oracle -- fuzzy query a field according to the initial of Pinyin
There is light ahead 2021-07-20 04:50:18

Preface

Oracle Database fuzzy query data , It can be used like keyword ;

however , Sometimes in business, you need to query a field according to the initial of Pinyin , At this time, there are more considerations

Problem solving

The solution is 3 Kind of :

1. Add a field to the table to represent the initials of Pinyin

Add a field to the table , It is used to store the initials of the first Pinyin of the customer's last name . In the future, you can inquire directly name like  '%name%' Just OK 了 . But it's not good for data maintenance , And if there are too many fields for fuzzy query in business , You need to add more fields to meet your needs ;

2. utilize gb2312 code

utilize gb2312 The coding , For example a Begin with B0A1 To B0C4 So the query is direct select * from Table where substr(name,0,1) between 'B0A1' and 'B0C4'   You can directly find out the data you need . I don't know if this method is feasible , Because now the database code is utf-8, Not applicable

3. utilize oracle function , Translate the name into the first letter of the last name

CREATE OR REPLACE FUNCTION F_TRANS_PINYIN_CAPITAL(P_NAME IN VARCHAR2) RETURN VARCHAR2 AS
V_COMPARE VARCHAR2(100);
V_RETURN VARCHAR2(4000);
FUNCTION F_NLSSORT(P_WORD IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN NLSSORT(P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
END;
BEGIN
FOR I IN 1..LENGTH(P_NAME) LOOP
V_COMPARE := F_NLSSORT(SUBSTR(P_NAME, I, 1));
IF V_COMPARE >= F_NLSSORT(' Acridine ') AND V_COMPARE <= F_NLSSORT(' Oh ') THEN
V_RETURN := V_RETURN || 'a';
ELSIF V_COMPARE >= F_NLSSORT(' 8、 ... and ') AND V_COMPARE <= F_NLSSORT(' Book ') THEN
V_RETURN := V_RETURN || 'b';
ELSIF V_COMPARE >= F_NLSSORT(' Cha ') AND V_COMPARE <= F_NLSSORT(' Wrong ') THEN
V_RETURN := V_RETURN || 'c';
ELSIF V_COMPARE >= F_NLSSORT(' Oh ') AND V_COMPARE <= F_NLSSORT(' Well ') THEN
V_RETURN := V_RETURN || 'd';
ELSIF V_COMPARE >= F_NLSSORT(' I'm sorry ') AND V_COMPARE <= F_NLSSORT(' Oh ') THEN
V_RETURN := V_RETURN || 'e';
ELSIF V_COMPARE >= F_NLSSORT(' Hair ') AND V_COMPARE <= F_NLSSORT(' I'm sorry ') THEN
V_RETURN := V_RETURN || 'f';
ELSIF V_COMPARE >= F_NLSSORT(' I'm sorry ') AND V_COMPARE <= F_NLSSORT(' Well ') THEN
V_RETURN := V_RETURN || 'g';
ELSIF V_COMPARE >= F_NLSSORT(' 妎 ') AND V_COMPARE <= F_NLSSORT(' I don't know ') THEN
V_RETURN := V_RETURN || 'h';
ELSIF V_COMPARE >= F_NLSSORT(' No ') AND V_COMPARE <= F_NLSSORT(' I'm sorry ') THEN
V_RETURN := V_RETURN || 'j';
ELSIF V_COMPARE >= F_NLSSORT(' Kaka ') AND V_COMPARE <= F_NLSSORT(' I'm sorry ') THEN
V_RETURN := V_RETURN || 'k';
ELSIF V_COMPARE >= F_NLSSORT(' Garbage ') AND V_COMPARE <= F_NLSSORT(' Yes ') THEN
V_RETURN := V_RETURN || 'l';
ELSIF V_COMPARE >= F_NLSSORT(' Mmm ') AND V_COMPARE <= F_NLSSORT(' Oh ') THEN
V_RETURN := V_RETURN || 'm';
ELSIF V_COMPARE >= F_NLSSORT(' Yeah ') AND V_COMPARE <= F_NLSSORT(' Malaria ') THEN
V_RETURN := V_RETURN || 'n';
ELSIF V_COMPARE >= F_NLSSORT(' What's the matter ') AND V_COMPARE <= F_NLSSORT(' Retting ') THEN
V_RETURN := V_RETURN || 'o';
ELSIF V_COMPARE >= F_NLSSORT(' 妑 ') AND V_COMPARE <= F_NLSSORT(' Exposure ') THEN
V_RETURN := V_RETURN || 'p';
ELSIF V_COMPARE >= F_NLSSORT(' 7、 ... and ') AND V_COMPARE <= F_NLSSORT(' What are you doing ') THEN
V_RETURN := V_RETURN || 'q';
ELSIF V_COMPARE >= F_NLSSORT(' Yeah ') AND V_COMPARE <= F_NLSSORT(' Well ') THEN
V_RETURN := V_RETURN || 'r';
ELSIF V_COMPARE >= F_NLSSORT(' Three ') AND V_COMPARE <= F_NLSSORT(' 蜶 ') THEN
V_RETURN := V_RETURN || 's';
ELSIF V_COMPARE >= F_NLSSORT(' 侤 ') AND V_COMPARE <= F_NLSSORT(' Well ') THEN
V_RETURN := V_RETURN || 't';
ELSIF V_COMPARE >= F_NLSSORT(' 屲 ') AND V_COMPARE <= F_NLSSORT(' It's just ') THEN
V_RETURN := V_RETURN || 'w';
ELSIF V_COMPARE >= F_NLSSORT(' evening ') AND V_COMPARE <= F_NLSSORT(' Oh ') THEN
V_RETURN := V_RETURN || 'x';
ELSIF V_COMPARE >= F_NLSSORT(' Ya ') AND V_COMPARE <= F_NLSSORT(' Rhyme ') THEN
V_RETURN := V_RETURN || 'y';
ELSIF V_COMPARE >= F_NLSSORT(' Yuan ') AND V_COMPARE <= F_NLSSORT(' A kind of ') THEN
V_RETURN := V_RETURN || 'z';
END IF;
END LOOP;
RETURN V_RETURN;
END;

Use this function to :

You can take out your name first , Then take the first letter of the Chinese character , It can be used as a query condition

select t.name from table_name t where f_trans_pinyin_capital(t.name) = 'ls';

reference :

[1].https://blog.csdn.net/wangheid/article/details/5288216

[2].https://www.cnblogs.com/telwanggs/p/6855491.html

Please bring the original link to reprint ,thank
Similar articles

2021-06-11

2021-06-15

2021-06-20

2021-06-20

2021-06-29

2021-07-12