Char And int

Question

Char And int

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

Jan Van der Eecken (10/15/2012)
Nice one, Tom! But does the TRY_CONVERT() make the query non-sargeable? Guess it would?

Yes it does...

Type conversion in expression (TRY_CAST([test].[field1] AS smallint)) may affect "SeekPlan" in query plan choice

However, the impact of that can be lowered by an additional BETWEEN...

SELECT * FROM TEST

WHERE TRY_CONVERT(tinyint,field_one) BETWEEN 1 AND 9

AND field_one BETWEEN '1' AND '9'

...at least on my simple test this allowed an index seek again - although I didn't SELECT * :w00t:

Edit: Copied wrong warning - switched now to seek plan...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

TomThomson SSC Guru Points: 104773 More actions · Answer 2

mister.magoo (10/15/2012)
Jan Van der Eecken (10/15/2012)
Nice one, Tom! But does the TRY_CONVERT() make the query non-sargeable? Guess it would?
Yes it does...
Type conversion in expression (TRY_CAST([test].[field1] AS smallint)) may affect "SeekPlan" in query plan choice
However, the impact of that can be lowered by an additional BETWEEN...
SELECT * FROM TEST
WHERE TRY_CONVERT(tinyint,field_one) BETWEEN 1 AND 9
AND field_one BETWEEN '1' AND '9'
...at least on my simple test this allowed an index seek again - although I didn't SELECT * :w00t:
Edit: Copied wrong warning - switched now to seek plan...

The trouble with the additional BETWEEN is that it changes the semantics: ' 1' is no longer accepted, and neither is '01'. Of course the requirement (as far as we've seen it) is not clear enough to say whether those values should be accepted or not.

Tom

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

L' Eomot Inversé (10/15/2012)

mister.magoo (10/15/2012)
The trouble with the additional BETWEEN is that it changes the semantics: ' 1' is no longer accepted, and neither is '01'. Of course the requirement (as far as we've seen it) is not clear enough to say whether those values should be accepted or not.

Too true. If this data is going to be queried more than once, I would be tempted to add a new column with parsed values that can be indexed and used in future selects. If it is just a one-off then indexes be damned, get it done and ditch the horrible data.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Just noticed the thread is a bit dated, but it seemed there was no real answer...

Why make it so complicated?

As per OP's request he just wants single chars 1 through 9 from an arbitrary string value..

(referencing the original testcase)

SELECT * FROM Test WHERE field_one LIKE '[1-9]'

=;o)

/Kenneth

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

ChrisM@home (10/15/2012)
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.

To be clear, I made a classic error in that post and corrected it. ISNULL() isn't sargeable especially when used by itself. It can give the appearance of being sargable when used with other criteria and won't necessarily prevent a seek if the other criteria come into play first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

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?

To answer this old post, the problem is that you're working with left justiied "words" that look like numbers which changes the rules quite a bit. You need to convert the things that look like numbers to actual numbers to use such a query OR make the "words" that look like numbers be left padded/right justified for this type of selection to work. That will also mean there's no chance of getting an index seek out of this and is one of the primary reasons why you MUST use the correct datatype for the given table.

You could make a persisted computed column for the conversion to numbers which would be indexable but it would be far better to use the correct datatype on the original column, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Kenneth Wilhelmsson (11/19/2012)
Just noticed the thread is a bit dated, but it seemed there was no real answer...
Why make it so complicated?
As per OP's request he just wants single chars 1 through 9 from an arbitrary string value..
(referencing the original testcase)
SELECT * FROM Test WHERE field_one LIKE '[1-9]'
=;o)
/Kenneth

Presumably you think that

select COUNT(*) from (select CHAR(I) as c from Tally where I < 256) Z

where c like '[1-9]'

delivers 9 in whatever his default collation is? I suspect that's rather unlikely: on a default installation it delivers 12.

Tom

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

I merely think that it produces the expected result according to the OP's presented scenario. 😉

However, should there be that his data actually *could* contain rows that had any of the three 'extra' superscript annotations as the sole character on that row,

then they may be filtered out as exceptions..

OTOH, he may want them included, since they may be considered variations on 1, 2, 3... Who knows?

/Kenneth

L' Eomot Inversé (11/19/2012)
Kenneth Wilhelmsson (11/19/2012)
Just noticed the thread is a bit dated, but it seemed there was no real answer...
Why make it so complicated?
As per OP's request he just wants single chars 1 through 9 from an arbitrary string value..
(referencing the original testcase)
SELECT * FROM Test WHERE field_one LIKE '[1-9]'
=;o)
/Kenneth
Presumably you think that
select COUNT(*) from (select CHAR(I) as c from Tally where I < 256) Z
where c like '[1-9]'
delivers 9 in whatever his default collation is? I suspect that's rather unlikely: on a default installation it delivers 12.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (11/19/2012)
ChrisM@home (10/15/2012)
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.
To be clear, I made a classic error in that post and corrected it. ISNULL() isn't sargeable especially when used by itself. It can give the appearance of being sargable when used with other criteria and won't necessarily prevent a seek if the other criteria come into play first.

Hi Jeff, thanks for the clarification and apologies for the tardy reply - up to my ears in stuff at the moment.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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