multi-lingual begins with search
How can you do a begins-with search that is insensitive to case and
other multi-lingual details?
Consider:
CREATE TABLE MYTABLE(ID NVARCHAR2(10), PRIMARY KEY (ID), MYNAME NVARCHAR2(2000));
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('A',N'Harry Peters');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('B',N'HARRY JAMES');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('C',N'h'||CHR(193 USING NCHAR_CS)||N'RRY JOHNSON');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('D',N'harry More');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('E',N'I after');
COMMIT;
CREATE INDEX MYTABLE_BIN ON MYTABLE(MYNAME);
CREATE INDEX MYTABLE_AI ON MYTABLE(NLSSORT(MYNAME,'NLS_SORT=''BINARY_AI'''));
How can an indexed select be done to retrieve all names that start with "harry"?
Consider:
CREATE TABLE MYTABLE(ID NVARCHAR2(10), PRIMARY KEY (ID), MYNAME NVARCHAR2(2000));
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('A',N'Harry Peters');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('B',N'HARRY JAMES');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('C',N'h'||CHR(193 USING NCHAR_CS)||N'RRY JOHNSON');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('D',N'harry More');
INSERT INTO MYTABLE(ID,MYNAME) VALUES ('E',N'I after');
COMMIT;
CREATE INDEX MYTABLE_BIN ON MYTABLE(MYNAME);
CREATE INDEX MYTABLE_AI ON MYTABLE(NLSSORT(MYNAME,'NLS_SORT=''BINARY_AI'''));
How can an indexed select be done to retrieve all names that start with "harry"?
0