How to Take data from an SQL table by referencing only the first position of the VARCHAR type

Question

How to Take data from an SQL table by referencing only the first position of the VARCHAR type

Asked 4 years, 11 months ago

Viewed 43 times

1

I need to delete everything that starts with '9' in my seg1 column, for example:

If in the table this '954747A' will be eliminated because the 9 is the first position.

If you have '34465922A' It doesn’t have to be deleted.

The logic I was doing is killing everything related to 9.

Segue Query:

SELECT DISTINCT A.SEG1 ||'-'|| a.SEG2 ||'-'|| A.SEG3
FROM TB_TESTE
WHERE A.SEG3 NOT LIKE '%C%'   
   AND A.SEG3 NOT LIKE '%D%' 
   AND A.SEG3 NOT LIKE '%E%' 
   AND A.SEG3 NOT LIKE '%F%'  
   AND A.SEG3 NOT LIKE '%G%' 
   AND A.SEG1 NOT LIKE '%9%';

Could you put how it returns, and what is related in SEG1? where this numbers Voce wants to eliminate, because in case would a notlike"%9" in the case

– DbaAlone

2019/08/07 at 13:24
DELETE FROM TB_TESTE WHERE SEG1 LIKE '9%';

– anonimo

2019/08/07 at 14:00
@anonimo I cannot delete everything from table I put only an abbreviation of table name now as example.

– LordDual

2019/08/07 at 14:35
@anonimo is an important table, I am making this rule only for that system. but in others it is used normally. but obg by the help

– LordDual

2019/08/07 at 14:36
But the above command is in line with what you said in "I need to delete everything that starts with '9' in my Second column". Unless by "delete" you mean "do not recover", in this case replace DELETE with SELECT.

– anonimo

2019/08/07 at 15:40

1 answer

Browser other questions tagged sql oracle pl-sql

You are not signed in. Login or sign up in order to post.

by DbaAlone • **424** points · Answer 1 · 2019-08-07T13:25:57+00:00

Try something like this because the LIKE with the % at the beginning will catch only those that start

SELECT DISTINCT A.SEG1 ||'-'|| a.SEG2 ||'-'|| A.SEG3
FROM TB_TESTE
WHERE A.SEG3 NOT LIKE '%C%'   
   AND A.SEG3 NOT LIKE '%D%' 
   AND A.SEG3 NOT LIKE '%E%' 
   AND A.SEG3 NOT LIKE '%F%'  
   AND A.SEG3 NOT LIKE '%G%' 
   AND A.SEG1 NOT LIKE '%9';