Use BETWEEN between strings

Question

Post reply

Use BETWEEN between strings

fiaolle

SSC Enthusiast

Points: 163
More actions
March 19, 2010 at 3:49 am

#218026

Hi
Is there a way to use BETWEEN between strings? I have a field fldPrice which has the data type nvarchar. Now I want to search for prices between som values. I tried with
SELECT fldPrice
FROM dbo.tblPrice
WHERE (fldPrice BETWEEN '10 000' AND '100 000')
but that just gave me the values '10 000' and '100 000'.
Please HELP
Thanks
Fia

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

SELECT *

FROM [dbo].[tblPrice]

WHERE [fldPrice] => 10000

AND [fldPrice] =< 100000

-EDIT- sorry, wrote between 1 and 10 instead of your values.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

This is a classic example of why you should use the correct datatype.

Between does work on strings but you are applying different rules just because you have string that looks like a number its not going to be processed as such

Try this example

with ctenums

as

(

select '100000000000000000000000' as price

union

select '10 00'

)

Select * from ctenums

where Price between '1' and '9'

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

fiaolle (3/19/2010)
SELECT fldPrice FROM dbo.tblPrice WHERE (fldPrice BETWEEN '10 000' AND '100 000')

Fia,

If fldPrice contains prices, consider using the MONEY or SMALLMONEY data types to store it. See http://msdn.microsoft.com/en-us/library/ms179882.aspx

Example:

DECLARE @Prices

TABLE (

price MONEY NOT NULL

);

INSERT @Prices (price) VALUES ($10.00);

INSERT @Prices (price) VALUES ($25.30);

INSERT @Prices (price) VALUES ($7.99);

INSERT @Prices (price) VALUES ($14.95);

SELECT price

FROM @Prices

WHERE price BETWEEN $12.50 AND $30;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

fiaolle SSC Enthusiast Points: 163 More actions · Answer 4

fiaolle

SSC Enthusiast

Points: 163

March 19, 2010 at 5:28 am

#1136606

Thank's

For the help:-)

Fia

Paul White SSC Guru Points: 150467 More actions · Answer 5

fiaolle (3/19/2010)
Thanks for the help 🙂

No worries.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi