Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six a-CodePudding

I've the following table.

CREATE TABLE Worker (
    WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FIRST_NAME CHAR(25),
    LAST_NAME CHAR(25),
    SALARY INT(15),
    JOINING_DATE DATETIME,
    DEPARTMENT CHAR(25)
);

INSERT INTO Worker 
    (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
        (001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
        (002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
        (003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
        (004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
        (005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
        (006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
        (007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
        (008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');

I'm looking for a way to get the desired result of the following question using the AND function.

Any solutions on the same would be appreciated.

CodePudding user response：

Try this:

SELECT * from Wroker
WHERE RIGHT(FIRST_NAME, 1) = 'h' 
      AND LENGTH(FIRST_NAME) = 6

CodePudding user response：

The shortest option is likely this (will become bad to read if the length increases from 6 to 10, 15 etc. due to many underscores):

SELECT *
FROM Worker
WHERE 
  FIRST_NAME LIKE '_____h'

Especially for a high length, I would avoid this and use LIKE and LENGTH:

SELECT *
FROM Worker
WHERE 
  LENGTH(FIRST_NAME) = 6 AND
  FIRST_NAME LIKE '%h'

Another similar option is RIGHT and LENGTH:

SELECT *
FROM Worker
WHERE 
  LENGTH(FIRST_NAME) = 6 AND
  RIGHT(FIRST_NAME,1) = 'h'

See here

Note: The answer assumes you just want to check whether exactly six characters appear. Edit your question - and if you know how - also the query - if this is not sufficient and you need to exactly check for example for six letters instead (ignoring spaces, digits etc.) or anything else.

CodePudding user response：

An other way to do it using SUBSTRING to extract from the sixth character and check if equal h :

SELECT * from Worker
WHERE SUBSTRING(FIRST_NAME, 6) = 'h'

CodePudding user response：

SELECT * FROM Worker                                                         
Where FIRST_NAME like "%h" and length(FIRST_NAME)=6

Take reference from image for the output