CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Clem_Fandango
Clem_Fandango

Reputation: 364

RTRIM in where clause

I'm using Microsoft SQL Server 2012 Management Studio. In my table below I'm trying to get rid of the ID numbers from 1st Jan 2019 to 4th Jan 2019 that are 5 digits long or after the dash begin with a zero. 

IDnum        DateTime
-----------------------
11-102434    03/01/2019    
11-02434     03/01/2019 
11-102421    02/01/2019
11-02421     02/01/2019
10-02345     31/12/2018

This is what I would like to see 

IDnum        DateTime
-------------------------
11-102434    03/01/2019    
11-102421    02/01/2019
10-02345     31/12/2018

I'm thinking there needs to be some kind of RTRIM() in the where clause but not sure how to do this.

Upvotes: 2

Views: 206

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Isn't this a relatively simple where clause?

where not (datetime >= '2019-01-01' and datetime < '2019-01-05' and
           (idnum like '%-_____' or
            idnum like '%-0%'
           )
          )

Upvotes: 3

Zack
Zack

Reputation: 2341

Reading your question, you have 2 main criteria in your excluded data:

  1. The date is between 2019-01-01 and 2019-01-04, AND
  2. Either the length of the IDNum is 5 characters (after the dash) OR the IDNum (after the dash) begins with a 0.

As with @Gordon's answer, you can wrap this up in a NOT:

WHERE NOT
(
    [DateTime] >= '2019-01-01'
    AND [DateTime] <= '2019-01-04'
    AND
    (
        IDNum LIKE '%-0%'
        OR IDnum LIKE '%-_____'
    )
)

With De Morgan's laws, we can simplify this a bit (or at least distribute the NOT):

WHERE
(
    [DateTime] < '2019-01-01'
    OR [DateTime] > '2019-01-04'
    OR
    (
        IDNum NOT LIKE '%-0%'
        AND IDnum NOT LIKE '%-_____'
    )
)

Upvotes: 1

Zorkolot
Zorkolot

Reputation: 2027

Here's one way. 

SELECT IDnum, DateTime
  FROM YourTable
 WHERE NOT (DateTime >= '2019-01-01' AND DateTime < '2019-01-05' --exclude from 1st Jan 2019 to 4th Jan 2019
             AND (IDnum LIKE '%-_____'  --that are 5 digits long after the dash
                  OR 
                  IDnum LIKE '%-0%'  --or begin with a 0 (after the dash)
                 )
           )

Upvotes: 2

Papai from BEKOAIL
Papai from BEKOAIL

Reputation: 1539

if your purpose is to meet this two following conditions:=

  1. DateTime should be in between 1st January to 4th January, 2019

  2. In IDNum the length of number after '-' should be more or equal than 5, then you probably no need of rtrim.

code:

Select * from Test where DateTime >= '01-01-2019' and DateTime <= '01-03-2019' and ((SUBSTRING(IDNum, 4, 1) = 0) or LEN(SUBSTRING(IDNum, 4, LEN(IDNum)-1)) >= 5);

though i am not sure how 31-12-2018 are present in your expected result. :D

Upvotes: 0

Related Questions