-1

I am trying to find the data using like operator. I am new into it and unsure how to find data. Below is input and output that I require but with my query that I am getting dropdown also which I don't want.

Input:

drop
drop1
drop15
drop20
drop30
dropdown

output:

drop
drop1
drop15
drop20
drop30

I have tried

select * From test where input like 'drop%'

but in output getting dropdown as well which is not required.Can someone help.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sak
  • 61
  • 6

2 Answers2

1

You can do this using the NOT LIKE operator.

SELECT * FROM test WHERE input LIKE 'drop%' AND input NOT LIKE 'dropdown%'
jarlh
  • 42,561
  • 8
  • 45
  • 63
Tarikh
  • 11
  • 1
  • Thank you Tarikh.Just checking if there is any way to find out if it's like drop or drop followed by number. – Sak Jul 31 '23 at 10:18
1

As Teradata supports regular expression, you can use regexp_similar to find the patterns you like.

With https://regex101.com/ you can test patterns

SELECT * FROM test WHERE regexp_similar(input, 'drop[0-9]*') = 1;
dnoeth
  • 59,503
  • 4
  • 39
  • 56
nbk
  • 45,398
  • 8
  • 30
  • 47
  • 1
    Should have an end of match anchor `$` to prevent `drop123any` – astentx Jul 31 '23 at 10:53
  • Thank you,just checking if there is anything we can do with normal like .Example if its like 'drop' or like 'drop followed by any number' not sure how to write that.Also i tried regexp_like it doesn't look correct.There is regexp_similar but not regexp_like – Sak Jul 31 '23 at 11:31
  • no the link shows what teradata supports regexp_similar doesn't belong to it, the "normal" like will not do it, if there should only be numbers following a text – nbk Jul 31 '23 at 11:37
  • 1
    Your link is showing syntax for Presto, but Teradata is PCRE-compatible and there's no `regexp_like`. In your case it's `WHERE regexp_similar(input 'drop[0-9]*') = 1` – dnoeth Jul 31 '23 at 11:59
  • 1
    @Sak after reading dnoeth comment i corrected my answer – nbk Jul 31 '23 at 13:38
  • 1
    And for regexp_similar, the pattern must match the entire string - in other words it behaves as if the pattern starts with ^ and ends with $ (whether explicitly specified or not) – Fred Jul 31 '23 at 15:20
  • @dnoeth-thank you...will it work in case we have the data like abc_test_drop1,cde_drop etc? if we give like this regexp_similar(tablename , '%_Drop[0-9]*') = 1? – Sak Jul 31 '23 at 16:44
  • @fred-great point,thank you so much. – Sak Jul 31 '23 at 16:44