Last two characters

Transact-SQL
1

i have the column(char(6)) i need to remove the first char starting with 1 and last 2 chars ending with 00
need help how to write the correct select statement.

select * from table
where
code(char(6)) = (last two char(code)!=00 and first char(code) !=1)

Example:
Code = 240500(should be there)
99999D(should be there)
100100(should not be there)
199999(should not be there)

2

Not sure why the first one SHOULD be there and the last one should NOT be there?

If "Starts with 1" or "Ends will 00" perhaps?

If I've got that right then I would do something like:

select * 
from table 
where  NOT
    (
            code LIKE '1%00'
         OR code LIKE '%00'
    )

or

...
where     code NOT LIKE '1%00'
      AND code NOT LIKE '%00'
    )

Note that because [code] is CHAR, rather than VARCHAR, there may be issues with trailing spaces. You might want to use RTrim(code) in place of "code"

Note also that if [code] can be NULL then the behaviour of both these examples won't include that row

3

Your example is a bit confusing. I thought I had a way but I am not sure now.

  1. Are values with 1 as the first character allowed
  2. Your code state != '00' but 240500 is okay?

To return everything that does not begin with a 1 try

SELECT * FROM table WHERE
code NOT LIKE '1%'
4

Thanks for ur reply

i am not getting 2405 00.

5

i am looking for

2405 00(should be there)
99999D(should be there)
1 001 00(should not be there)
1 99999(should not be there)

6

Yeah, but we are not understanding what the definition of your requirement is.

How do you define

2405 00(should be there)

and

1 001 00(should not be there)

Do you just want to exclude things that start with "1"?

Your O/P was "i need to remove the first char starting with 1 and last 2 chars ending with 00"

7

see its quit clear , here there are two conditions.
the code starting from 1 and ending from 00 should be removed like--- 1 001 00(should not be there).
and
the code 2405 00(should be there) because its not starting from 1.
i hope its clear now.

8

So maybe:

SELECT * FROM table WHERE
code NOT LIKE '1%00'
9

OK, I've got that, so why is this excluded?