Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1112131415

wildcards Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 11:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:33 PM
Points: 8,571, Visits: 9,076
Drew Copenhaver (9/16/2013)
Steve Jones - SSC Editor (9/16/2013)
Drew Copenhaver (9/16/2013)
Yeah, my initial thought was "2, if my instance of 2008R2 properly handles regex, we'll see..." So I ran the code and got the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

So I ended up choosing 0, assuming that was the stand-in for an error message. I'unno, maybe it works properly on 2012?

Not saying I deserve a point or anything, I certainly was wrong. I guess I'm just asking: If it's not specified in the question, are we meant to always assume the newest version of SQL Server?


I'm not sure that's true. I ran this on my R2 instance and it worked. The regex stuff in LIKE should have been there far earlier. I don't have a SS2K or SS2K5 instance handy, but I think it worked then.

Your error is a syntax error, which might be a transcription or cut/paste error.


My mistake. I don't think it's a syntax or copy past error, though. We've got two servers running different instances of SQL here. I played with the syntax and couldn't get it to work on what I believe is the 2008 box, but it did work on our other server which is running 2005 and it ran with the correct results. I'm stumped. Might just ask our DBA later and see what he thinks.

Given the line the error was on, it's in the values clause not in the regexp. The ability to specify more than one row in the values clause was not in SQL 2000 or 2005, but is in SQL 2008 (and onwards). That's your syntax error detecded at a comma, which must have occurred running it against 2005 or an earlier version, where the insert statement has invalid syntax. So perhaps you have mistaken the 2005 box for the 2008 box and vice versa?


Tom
Post #1495200
Posted Monday, September 16, 2013 12:17 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:47 AM
Points: 584, Visits: 856
L' Eomot Inversé (9/16/2013)
Drew Copenhaver (9/16/2013)
Steve Jones - SSC Editor (9/16/2013)
Drew Copenhaver (9/16/2013)
Yeah, my initial thought was "2, if my instance of 2008R2 properly handles regex, we'll see..." So I ran the code and got the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

So I ended up choosing 0, assuming that was the stand-in for an error message. I'unno, maybe it works properly on 2012?

Not saying I deserve a point or anything, I certainly was wrong. I guess I'm just asking: If it's not specified in the question, are we meant to always assume the newest version of SQL Server?


I'm not sure that's true. I ran this on my R2 instance and it worked. The regex stuff in LIKE should have been there far earlier. I don't have a SS2K or SS2K5 instance handy, but I think it worked then.

Your error is a syntax error, which might be a transcription or cut/paste error.


My mistake. I don't think it's a syntax or copy past error, though. We've got two servers running different instances of SQL here. I played with the syntax and couldn't get it to work on what I believe is the 2008 box, but it did work on our other server which is running 2005 and it ran with the correct results. I'm stumped. Might just ask our DBA later and see what he thinks.

Given the line the error was on, it's in the values clause not in the regexp. The ability to specify more than one row in the values clause was not in SQL 2000 or 2005, but is in SQL 2008 (and onwards). That's your syntax error detecded at a comma, which must have occurred running it against 2005 or an earlier version, where the insert statement has invalid syntax. So perhaps you have mistaken the 2005 box for the 2008 box and vice versa?


That syntax works only in 2008 onwards

http://technet.microsoft.com/en-us/library/dd776382.aspx

Cheers
Post #1495226
Posted Monday, September 16, 2013 12:29 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 9:29 AM
Points: 307, Visits: 72
L' Eomot Inversé (9/16/2013)
Drew Copenhaver (9/16/2013)
Steve Jones - SSC Editor (9/16/2013)
Drew Copenhaver (9/16/2013)
Yeah, my initial thought was "2, if my instance of 2008R2 properly handles regex, we'll see..." So I ran the code and got the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.

So I ended up choosing 0, assuming that was the stand-in for an error message. I'unno, maybe it works properly on 2012?

Not saying I deserve a point or anything, I certainly was wrong. I guess I'm just asking: If it's not specified in the question, are we meant to always assume the newest version of SQL Server?


I'm not sure that's true. I ran this on my R2 instance and it worked. The regex stuff in LIKE should have been there far earlier. I don't have a SS2K or SS2K5 instance handy, but I think it worked then.

Your error is a syntax error, which might be a transcription or cut/paste error.


My mistake. I don't think it's a syntax or copy past error, though. We've got two servers running different instances of SQL here. I played with the syntax and couldn't get it to work on what I believe is the 2008 box, but it did work on our other server which is running 2005 and it ran with the correct results. I'm stumped. Might just ask our DBA later and see what he thinks.

Given the line the error was on, it's in the values clause not in the regexp. The ability to specify more than one row in the values clause was not in SQL 2000 or 2005, but is in SQL 2008 (and onwards). That's your syntax error detecded at a comma, which must have occurred running it against 2005 or an earlier version, where the insert statement has invalid syntax. So perhaps you have mistaken the 2005 box for the 2008 box and vice versa?


Yes, that's actually what I was thinking, too. I just asked our DBA and that is indeed the case. I had my servers swapped. I figured it might be something like that. I remember having a similar issues a few months ago because one of the servers supported what I wanted to do and the other (the one I needed) didn't. Thanks for the help, guys!
Post #1495233
Posted Wednesday, September 18, 2013 2:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, Visits: 319
ksatpute123 (9/11/2013)
Ahh! Too many posts and none adding any value to the topic of discussion.

Wrong question or not, if it was able to teach you something new or refresh your memory about things you knew but had forgotten then it's worth it.

Go write your own QoTD and share knowledge with everyone.


Seconded
Post #1495800
Posted Wednesday, September 18, 2013 2:17 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, Visits: 319
Koen Verbeeck (9/11/2013)
Vobr Jiri (9/11/2013)
In SS2k you must use in T-SQL script this version:

INSERT @i VALUES ('Steve')
INSERT @i VALUES ('Stephan')
INSERT @i VALUES ('Stephanie')
INSERT @i VALUES ('Sterling')
INSERT @i VALUES ('Steppenwolf')

Then are 4 rows in the table @i inserted and correct answer is 2.


SQL Server 2000 is no longer considered as a possible edition in the Questions.


That's a shame, we've still got three or four customers using it who are totally resistant to moving to 2005 let alone 2008 or later.
Post #1495802
Posted Wednesday, September 18, 2013 9:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:25 AM
Points: 252, Visits: 226
nice question... i got +1

actually there is only nvarchar(50) only
DECLARE @i TABLE( mychar VARCHAR(50));

INSERT @i VALUES ('Steve'), ('Stephan'), ('Stephanie')
, ('Sterling'), ('Steppenwolf')

SELECT mychar
FROM @i
WHERE mychar LIKE 'Ste[^p]%'


Manik
You cannot get to the top by sitting on your bottom.
Post #1495960
Posted Thursday, September 19, 2013 9:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:19 AM
Points: 887, Visits: 1,226
I learnt something new so thanks Steve.
Post #1496465
« Prev Topic | Next Topic »

Add to briefcase «««1112131415

Permissions Expand / Collapse