October 10, 2012 at 3:00 am
Consider this Table :
CREATE TABLE Test ( field_one nvarchar(10) );
DECLARE @i int;
WHILE @i<= 1000
BEGIN
INSERT Test VALUES(CAST(@i AS nvarchar(10))
SET @i = @i + 1;
END
And also this Query:
SELECT * FROM Test WHERE field_one BETWEEN '1' AND '9'
if we run query we see all 1000 records,but we want to see '1','2','3',...,'9',
How we can revise query?
October 10, 2012 at 5:16 am
May I ask why are you storing an integer as an nvarchar?
October 10, 2012 at 5:52 am
As jan van asked why are you using nvarchar
You must have noticed that in your result some numbers are missing . This is actually charactor comparison.
If you want the correct result you may want something like
SELECT * FROM Test WHERE cast(field_one as int) BETWEEN '1' AND '9'
October 10, 2012 at 8:39 am
Eskandari (10/10/2012)
Consider this Table :CREATE TABLE Test ( field_one nvarchar(10) );
DECLARE @i int;
WHILE @i<= 1000
BEGIN
INSERT Test VALUES(CAST(@i AS nvarchar(10))
SET @i = @i + 1;
END
And also this Query:
SELECT * FROM Test WHERE field_one BETWEEN '1' AND '9'
if we run query we see all 1000 records,but we want to see '1','2','3',...,'9',
How we can revise query?
Perhaps you can explain what you are trying to do? There are a number of fundamental flaws in the ddl and the approach here. I am unclear if you trying to sort character data as an int, or if you want a comma separated list, or ....
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2012 at 9:22 am
in a field of a table ( with nvarchar data type) we have about 10000 data,some of them are integer - from 1 to 1000- and other are string;
I want to select data that are int and between 1 and 9, And so I write this query :
SELECT * FROM myTable WHERE field_one BETWEEN '1' and '9'
but i see wrong result and i want to correct it?
October 12, 2012 at 9:31 am
Eskandari (10/12/2012)
in a field of a table ( with nvarchar data type) we have about 10000 data,some of them are integer - from 1 to 1000- and other are string;I want to select data that are int and between 1 and 9, And so I write this query :
SELECT * FROM myTable WHERE field_one BETWEEN '1' and '9'
but i see wrong result and i want to correct it?
We can't see what you see. We don't know what your table or data look like. Things like "wrong result" don't provide any detail about what is wrong.
I am guessing that you are not getting the desired output because you have mixed datatypes. varchar values like '77' are between '1' and '9' because it is an ASCII sort, not a numeric sort.
;with cte as
(
select '1' as SomeVal union all
select '11' union all
select 'Some Non Number' union all
select '88'
)
select * from cte
where SomeVal between '1' and '9'
Is the above provide a reasonable example of what you are challenged with?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 13, 2012 at 12:33 am
yes
;with cte as
(
select '1' as SomeVal union all
select '11' union all
select 'Some Non Number' union all
select '88'
)
select * from cte
where SomeVal between '1' and '9'
And Result :
1
11
88
but I want To See :
1
October 13, 2012 at 2:20 am
-- A numeric column will sort numerically.
-- This column is character and will sort alphabetically
-- so you have to work around it. Here's one way.
;with cte as
(
select '1' as SomeVal union all
select '11' union all
select 'Some Non Number' union all
select '88'
)
select *
from cte
where RIGHT('0000'+SomeVal,5) between '00001' and '00009'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 13, 2012 at 7:42 pm
Eskandari (10/10/2012)
Consider this Table :CREATE TABLE Test ( field_one nvarchar(10) );
DECLARE @i int;
WHILE @i<= 1000
BEGIN
INSERT Test VALUES(CAST(@i AS nvarchar(10))
SET @i = @i + 1;
END
And also this Query:
SELECT * FROM Test WHERE field_one BETWEEN '1' AND '9'
if we run query we see all 1000 records,but we want to see '1','2','3',...,'9',
How we can revise query?
I can't understand why you see any records at all. You haven't inserted any records with field_one have a value other that null. Either you have some extremely weird settings controlling NULL handling, or what you've asked us about isn't what you've actually got.
Tom
October 14, 2012 at 2:27 am
L' Eomot Inversé (10/13/2012)
Eskandari (10/10/2012)
Consider this Table :CREATE TABLE Test ( field_one nvarchar(10) );
DECLARE @i int;
WHILE @i<= 1000
BEGIN
INSERT Test VALUES(CAST(@i AS nvarchar(10))
SET @i = @i + 1;
END
And also this Query:
SELECT * FROM Test WHERE field_one BETWEEN '1' AND '9'
if we run query we see all 1000 records,but we want to see '1','2','3',...,'9',
How we can revise query?
I can't understand why you see any records at all. You haven't inserted any records with field_one have a value other that null. Either you have some extremely weird settings controlling NULL handling, or what you've asked us about isn't what you've actually got.
The sample script fails with a syntax error. When that's fixed, it runs - but no rows are inserted into the table because @i is null and fails the WHILE test.
When that's fixed, the script populates the tables with values from @i to 1000, cast as NVARCHAR.
CREATE TABLE Test ( field_one nvarchar(10) );
DECLARE @i int;
SET @i = 0; -- correction; @i MUST be non-null otherwise table 'Test' will be empty
WHILE @i<= 1000
BEGIN
INSERT Test VALUES( CAST(@i AS nvarchar(10)) ) -- correction; right paren missing
SET @i = @i + 1;
END
SELECT * FROM Test WHERE field_one BETWEEN '1' AND '9'
-- (890 row(s) affected)
-- excluded rows are '90'-'99' and '900'-'999'
SELECT * FROM Test WHERE RIGHT('0000'+field_one,5) BETWEEN '00001' AND '00009'
-- (9 row(s) affected)
SELECT * FROM Test WHERE CAST(field_one AS INT) BETWEEN 1 AND 9
-- (9 row(s) affected)
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 14, 2012 at 8:57 am
ChrisM@home (10/14/2012)
....SELECT * FROM Test WHERE RIGHT('0000'+field_one,5) BETWEEN '00001' AND '00009'
This only works if there are no leading spaces. Leading zeroes are OK, and string comparison ignores trailing spaces. So ' 9' is left out, but '9 ' is OK as is '09 '. I think it would be improved by LTRIM
SELECT * FROM Test WHERE RIGHT('0000'+LTRIM(field_one),5) BETWEEN '00001' AND '00009'
Tom
October 15, 2012 at 6:23 am
Looks like you need to filter some kind of staging table...
That what you can try:
declare @t table (SomeVal nvarchar(100))
insert @t
select '1' union all
select '9' union all
select '11' union all
select 'Some Non Number' union all
select '88'
-- will be better to use tally table if you have one, otherwise use in-line tally
-- there are few ways, one of them:
;WITH cte AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT null)) RN
FROM sys.columns -- any will do
)
SELECt *
FROM @t
WHERE SomeVal IN (SELECT CAST(RN AS NVARCHAR(100)) FROM cte WHERE RN BETWEEN 1 AND 9)
You cannot expect BETWEEN to behave for strings same ways as for numbers.
It uses relevant sort algorithm, which would recognise that 11 is higher number than 9, but when sorting strings, it would place 1,11,111,1111 well before any string which starts from 2 (not even saying ones starting from 9 :-))
October 15, 2012 at 8:28 am
L' Eomot Inversé (10/14/2012)
I think it would be improved by LTRIM
SELECT * FROM Test WHERE RIGHT('0000'+LTRIM(field_one),5) BETWEEN '00001' AND '00009'
That's still not going to work - a string like 'GLUEGOO000009' will be picked as being in the 1 to 9 range because the last 5 characters are '00009'. The possibility of any arbitrary text being there means any test that throws away non-space characters won't fly.
If leading zeroes aren't allowed (so for example '09' doesn't count as being 9) it's easy:
SELECT * FROM Test WHERE LTRIM(RTRIM(field_one)) LIKE '[123456789]'
should do the job pretty well. Allowing leading zeroes is a bit messy though, needs something like
SELECT * FROM Test WHERE
'00'+LTRIM(RTRIM(field_one)) =
REPLICATE('0',1+LEN(LTRIM(RTRIM(field_one))))+RIGHT(RTRIM(field_one),1)
AND
RIGHT(LTRIM(field_one),1) LIKE '[123456789]'[/CODE]
At least it would be as complicated as that in SQL 2008 R2, but I've just noticed this topic is in an SQL 2012 forum, and in SQL 2012 there is an easier way to do it (I don't have SQL 2012, so haven't actually run this):
SELECT * FROM TEST WHERE TRY_CONVERT(tinyint,field_one) BETWEEN 1 AND 9[/CODE]
Tom
October 15, 2012 at 9:38 am
Nice one, Tom! But does the TRY_CONVERT() make the query non-sargeable? Guess it would?
October 15, 2012 at 10:05 am
Jan Van der Eecken (10/15/2012)
Nice one, Tom! But does the TRY_CONVERT() make the query non-sargeable? Guess it would?
Good point, Jan. I remember recently Howard W posted evidence that CAST(Datetime AS DATE) is sargable, and some time ago someone else - JBM I think - posted evidence that ISNULL() wasn't, at least in the test they employed. I'm surprised we don't have a table of sargable expressions x version.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply