CODEWITHSUNDEEP
sql-serversql-server-2008
ahmed mohamady
ahmed mohamady

Reputation: 362

How to select between two varchar columns in SQL Server

I try to select some rows between to values in SQL Server, but it not working.

In the past I made something like this in Oracle.

This is my code 

  Select * 
  From TABLENAME 
  Where CU_CODE Between 'ELCTRN/6601' and 'ELCTRN/6699'

Please can anyone help me?

Upvotes: 1

Views: 1767

Answers (3)

Krish
Krish

Reputation: 39

DECLARE @T TABLE (CU_CODE  NVARCHAR(50));

    INSERT INTO @T  
    VALUES('ELCTRN/6601'),
    ('ELCTRN/6602'),
    ('ELCTRN/6603'),
    ('ELCTRN/6605'),
    ('ELCTRN/668'),
    ('ELCTRN/661'),
    ('ELCTRN/662') ;

    SELECT * FROM 
    (
    SELECT CU_CODE,SUBSTRING(CU_CODE, PATINDEX('%[0-9]%', CU_CODE),       LEN(CU_CODE)) AS CODE FROM @T
    ) AS T 
    WHERE CODE BETWEEN 6601 AND 6603

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

There's nothing wrong with your approach... Try this:

DECLARE @tbl TABLE (CU_CODE  NVARCHAR(50));

INSERT INTO @tbl VALUES
  (N'ELCTRN/6601')
, (N'ELCTRN/6602')
, (N'ELCTRN/6603')
, (N'ELCTRN/6604')
, (N'ELCTRN/6605')
, (N'ELCTRN/6606')
, (N'ELCTRN/6607');

SELECT * 
FROM @tbl AS tbl
WHERE tbl.CU_CODE BETWEEN N'ELCTRN/6603' AND N'ELCTRN/6605'

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this :

Select  * 
From TABLENAME 
WHERE   CAST(RIGHT(CU_CODE,4) AS INT) Between 6601 and 6699

Upvotes: 1

Related Questions