SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Substring comprehension


Substring comprehension

Author
Message
dcawvive
dcawvive
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 149
Comments posted to this topic are about the item Substring comprehension
ShilpaSunil
ShilpaSunil
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 78
Refer this link
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/00000293.htm
davoscollective
davoscollective
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 1004
Took a guess and got it wrong, but...
Ran this on 2005 and A is not actually NULL, its an empty string. Maybe its NULL on 2008?
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3692 Visits: 4408
The correct answer is: A is '' (empty string), B is 'Chris'.
Try this code:
declare @name varchar(12)
select @name = 'Christopher'
select case when substring(@name,15,12) is null then 'NULL' else 'NOT NULL' end as A, substring(@name,-6,12) as B


and you will see the result:
A        B
-------- ------------
NOT NULL Chris

(1 row(s) affected)



Despite what is shown in 2008 BOL

Maybe the author of the question uses some "secret special" BOL ;-)
I use the following link: http://msdn.microsoft.com/en-us/library/ms187748.aspx, and what I see there:
SUBSTRING ( value_expression ,start_expression , length_expression )
...
If length_expression is negative, an error is generated and the statement is terminated.

So, length_expression cannot be negative, but the "-6" from the question is start_expression.

the code ... does not return the whole value expression when the sum of the start and length values are greater than the length of the variable

Just a little detail from BOL:
the whole value expression beginning at start_expression is returned
;-)
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1931 Visits: 1323
Just a little mistake, empty string and null.
But to tell the truth, I was confused, because I know the first expression returns empty string and not a null...



See, understand, learn, try, use efficient
© Dr.Plch
Toby White
Toby White
Right there with Babe
Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)Right there with Babe (753 reputation)

Group: General Forum Members
Points: 753 Visits: 639
Even though A is empty string versus Null the answer is still obvious given that it is multiple choice. In other words it was obvious that the author had just made a mistake between empty/null since options A and C were so far off.
BudaCli
BudaCli
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1254 Visits: 598
I chose the one closest to the answer and I seem to have got it right. Hihahhhhh

What you don't know won't hurt you but what you know will make you plan to know better
premkuttan
premkuttan
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 299
Can some one explains how it returns 'chris' when the start value is -6 and length is 12.
VM-723206
VM-723206
Right there with Babe
Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)Right there with Babe (758 reputation)

Group: General Forum Members
Points: 758 Visits: 267
sorry, wrong post..
mark.ross
mark.ross
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 37
If you think of it sort of as a number line, the name "Christopher" would occupy spots 1 thru 11 of the line. The expression is askiing you to look at spots -6 thru 5. This is 12 spots because of the number zero. The first 7 spots (-6 thru 0) we're asked to look at don't correspond to any characters in the name and don't return any characters. But the last 5 spots correspond to spots 1 thru 5 of the number line and those spots are occupied by the characters "Chris".

This is perhaps a clumsy example, but it seems to work.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search