NEED TO SEARCH SL NOS BY USING 6 DIGIT NO FROM SEARCH AS INPUT FROM COLUMN AS VARCHAR 2 DATA TYPE-CodePudding

create table TASK1 (
SL_NO NUMBER ,
DESCRIPTION VARCHAR2 (1000));


INSERT INTO TASK1 VALUES (1,'EMPLOYEE NO. (300546), EMPLOYEE NO T-4300546, EMPLOYEE NO 30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN APR22');

INSERT INTO TASK1 VALUES (2,'EMPLOYEE NO. (L300546), EMPLOYEE NO T-4300546K, EMPLOYEE NO GT30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN MAR22');

INSERT INTO TASK1 VALUES (3,'EMPLOYEE NO. (GT30056), EMPLOYEE NO T-4300546K, EMPLOYEE NO GT30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN FEB22');

INSERT INTO TASK1 VALUES (4,'EMPLOYEE NO. (-300546), EMPLOYEE NO T-4300546K, EMPLOYEE NO GT30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN JAN22');

I want to search all the sl no in which employee no 300546 is involved . i used like query for above result - select * from task1 where description like '00546%'; but it is listing all SL no where 300546 not effected in sl no 3. (Note while searching special charecter, alphabet like (), L, -T may be there in data along with 300546).

thank you.

CodePudding user response：

With your current table structure and description you can use:

select * from task1 where description like '%(300546)%'

But that's a bit of a hack. If you change anything about the description (add two emp numbers, or the parens aren't put it) that will no longer work. You should consider adding the employee number as a separate column. That way you'll be able to easily join for more employee information.

CodePudding user response：

while searching special character, alphabet like (), L, -T may be there in data along with 300546

If it can be EMPLOYEE NO. ( then an optional character and then the digits you want to match and then ) then you can use:

SELECT *
FROM   task1
WHERE  description LIKE 'EMPLOYEE NO. (300546)%'
OR     description LIKE 'EMPLOYEE NO. (_300546)%'

Which, for the sample data, outputs:

SL_NO DESCRIPTION

1 EMPLOYEE NO. (300546), EMPLOYEE NO T-4300546, EMPLOYEE NO 30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN APR22

2 EMPLOYEE NO. (L300546), EMPLOYEE NO T-4300546K, EMPLOYEE NO GT30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN MAR22

4 EMPLOYEE NO. (-300546), EMPLOYEE NO T-4300546K, EMPLOYEE NO GT30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN JAN22

SL_NO	DESCRIPTION
1	EMPLOYEE NO. (300546), EMPLOYEE NO T-4300546, EMPLOYEE NO 30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN APR22
2	EMPLOYEE NO. (L300546), EMPLOYEE NO T-4300546K, EMPLOYEE NO GT30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN MAR22
4	EMPLOYEE NO. (-300546), EMPLOYEE NO T-4300546K, EMPLOYEE NO GT30054698TT ARE AWARDED AS EMPLOYEE OF THE MONTH IN JAN22

db<>fiddle here