Question 20954 in Mysql Question posted in
The official MySQL documentation can be found here.

Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six alphabets? – Mysql

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.

Tags:

4

Answers


  1. 0

    Try this:

    SELECT * from Wroker
WHERE RIGHT(FIRST_NAME, 1) = 'h' 
      AND LENGTH(FIRST_NAME) = 6
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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'
    Login or Signup to reply.
  4. 0 
    SELECT * FROM Worker                                                         
Where FIRST_NAME like "%h" and length(FIRST_NAME)=6

    Take reference from image for the output

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search