What the problem with substring

Question

Post reply

What the problem with substring

nguyennd

SSCommitted

Points: 1881
More actions
March 31, 2010 at 4:10 am

#217211

I have a SQL code
select SUBSTRING('市市市市市市市市市市',40,1)
select SUBSTRING('市市市市市市市市市市',20,1)
Why result are
select SUBSTRING('市市市市市市市市市市',40,1) => ''(blank)
select SUBSTRING('市市市市市市市市市市',20,1)=>NULL
huhu
http://unitech.vn

Viewing 15 posts - 1 through 15 (of 17 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

When I ran your code I got blank on both lines. I ran it on SQL Server 2005.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

nguyennd (3/31/2010)
I have a SQL code
select SUBSTRING('??????????',40,1)
select SUBSTRING('??????????',20,1)
Why result are
select SUBSTRING('??????????',40,1) => ''(blank)
select SUBSTRING('??????????',20,1)=>NULL
huhu

No problem here, the behaviour is exactly as described in BOL where the start expression is greater than the number of characters in the value expression:

SELECT CASE

WHEN SUBSTRING('??????????',40,1) = ''

THEN 'Empty string, see BOL'

END

However:

select SUBSTRING('??????????',20,1)=>NULL

- you can't use = > < with NULL, instead use IS NULL or IS NOT NULL.

^{“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

nguyennd SSCommitted Points: 1881 More actions · Answer 3

My string is Shift-js code

I try some case. Example:

select SUBSTRING('??????????',20,1)

select SUBSTRING('??????????',21,1)

select SUBSTRING('??????????',22,1)

select SUBSTRING('??????????',23,1)

Are NULL

What the problem???

http://unitech.vn

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

Using 2005 and 2008,

select SUBSTRING('??????????',20,1)

returns an empty string.

Where does NULL come into this? Are you expecting NULL to be returned when the first expression e.g. 20 is greater than the number of characters in the value ('??????????')?

^{“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

nguyennd SSCommitted Points: 1881 More actions · Answer 5

nguyennd

SSCommitted

Points: 1881

March 31, 2010 at 5:08 am

#1142964

I don't know, but i use SQL 2008

http://unitech.vn

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

nguyennd (3/31/2010)
I don't know, but i use SQL 2008

1. What is the result when you run this:

select SUBSTRING('??????????',20,1)

2. Is this the result you expect to see?

3. If the result is not what you expect to see, then what is?

^{“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

nguyennd SSCommitted Points: 1881 More actions · Answer 7

When i run this SQL

select SUBSTRING('??????????',20,1)

It return NULL but i want it return blank. I can use ISNULL but this code

select SUBSTRING('??????????',41,1)

return blank

Why not same blank or same NULL ?

http://unitech.vn

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

nguyennd (3/31/2010)
When i run this SQL
select SUBSTRING('??????????',20,1)
It return NULL but i want it return blank.

It should return an empty string. It correctly returns an empty string on instances of 2k8 and 2k5 here.

^{“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

nguyennd SSCommitted Points: 1881 More actions · Answer 9

Yes, i think it will return blank but i can't belive why it return NULL 😀

http://unitech.vn

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

nguyennd (3/31/2010)
Yes, i think it will return blank but i can't belive why it return NULL 😀

Well I never. I reckon you've found...a bug.

Couldn't find anything on Google, not in 10 mins searching.

^{“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

HowardW One Orange Chip Points: 29109 More actions · Answer 11

Does potentially sound like some sort of bug. What's the server collation on this server?

If you change the code to the below

select SUBSTRING(N'??????????',40,1)

select SUBSTRING(N'??????????',20,1)

Does it have a different effect?

Wondering if something (literally) could be getting lost in translation pasting into this forum.

Could you run the following (typing out yourself rather than copying from this post):

SELECT UNICODE('?')

and let us know the number returned

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

When using string functions with Unicode data, make sure it is Unicode data:

SELECT SUBSTRING(N'??????????',20,1)

Notice the N prefix to the literal string.

edit: sorry HowardW, missed your post :blush:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

HowardW One Orange Chip Points: 29109 More actions · Answer 13

Paul White NZ (3/31/2010)
edit: sorry HowardW, missed your post :blush:

No worries 😀

I'm still kind of interested in why it would return NULL in one of those examples and blank in another. As far as I understand, they would both have been implicitly converted to a varchar(10) so should have the same behaviour for both queries.

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

HowardW (3/31/2010)
I'm still kind of interested in why it would return NULL in one of those examples and blank in another. As far as I understand, they would both have been implicitly converted to a varchar(10) so should have the same behaviour for both queries.

This area is just horrible.

http://en.wikipedia.org/wiki/Shift_JIS

Notice that CHAR returns NULL if it is passed a value outside the range 0-255. So, take a bunch of characters in the weird, and ill-defined, Shift-JIS encoding, throw it into a VARCHAR (single-bytes only), pass it through SUBSTRING, and you might get a NULL. Surprised? 😉

More fun:

SELECT N'??????????'; -- garbage

GO

PRINT N'??????????'; -- works

GO

SELECT name FROM fn_helpcollations() WHERE name LIKE N'%japanese_XJIS%';

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi