InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 20:40:51 GMT20http://www.sqlservercentral.com/Forums/Topic697897-338-1.aspx[quote][b]Jack Corbett (4/16/2009)[/b][hr]You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified.[/quote]Now that makes me feel honored:Wow:[quote] I didn't want you to think I was a fool.[/quote]Never would. And just to repeat - this "would have been" the first time I disagreed.So if you were a fool, then I would be one as well (because I mostly (99,9%) agree with what you say). But now don't get the feeling that you are on the safe side, since I actually may be a fool:-D (and sometimes I definitively am - just cannot get this human behaviour out of myself)Fri, 17 Apr 2009 00:28:39 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxI'm not one to throw stones... especially where the word "fool" is involved ;-)By the way, the respect is returned.Thu, 16 Apr 2009 14:34:31 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxI didn't really think we disagreed, I understood what you meant, and I wanted to make sure that you, and everyone else, understood what I meant. I also was trying to be quick and did not fully explain, or probably think through thoroughly, what I meant.You and Christian are among the folks I respect on SSC (fortunately a growing group) so I wanted to make sure I clarified. I didn't want you to think I was a fool.Thu, 16 Apr 2009 14:13:28 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxHey Jack:I think we're on the same page. I would never advocate that front-end editing be abandoned in favor of ONLY bouncing errors back from the server, but I would rather have redundant checks, and an occasional bounce, than allow data in that breaks a business rule. I've seen what happens with DBs created by developers who don't like constraints, or even foreign keys, because they "interfere with flexibility." (No, I don't think you would ever do this.)Let me clarify the "upper case collation". Bad choice of words on my part. Every time I get in a hurry I lose precision. I assumed (possibly in error) that the order numbers coming in are validated against an orders table. Using a case-sensitive collation would make the lower-case 'cqs' strings fail. (Of course, it might also cause unexpected duplicates, depending on how the applications are written.)The point was and is, if keeping those lower case values out is critical, steps can and should be taken to prevent it at the db level. Even if it's only changing the insert procedure to store UPPER(custOrderNo) instead of custOrderNo. In this case, it sounds as if it were more of an annoyance than a show-stopper.Regards, BobThu, 16 Apr 2009 14:04:52 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspx[quote][b]Christian Buettner (4/16/2009)[/b][hr][quote][b]Jack Corbett (4/16/2009)[/b][hr]Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.[/quote]Never thought I would ever have a different opinion than you, but never say never.I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition :-D(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)[/quote][quote][b]Bob Hovious (4/16/2009)[/b][hr][quote]Somehow users managed to input some of the customer order numbers as lower case [/quote]Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database. [/quote]I figured I'd hit both of these in one post.Christian,I'm not sure we disagree. The point I was trying to make, and obviously not clearly, is that a business rule should be enforced in the application so that you do not "waste" a round-trip to the DB with invalid data. Would I want to put some kind of validation in the database, too? Yes. I'd probably use UPPER in my insert/update procedures. If you aren't using stored procedures, but an ORM tool or ad-hoc SQL, you'd have to use an INSTEAD OF trigger in the database to make sure the data was inserted properly and apply the UPPER, otherwise it still has to be done in the application. Ideally you would use a case-sensitive collation for the column and a check constraint.Bob,I missed the part of the post you quoted, which causes us to assume that the application IS supposed to be validating the data. As I said above, that doesn't mean I wouldn't validate in the database as well, mainly to protect the database from some ad hoc update by me or another DBA. Is there an upper case only collation? I know you can do case-sensitive, but didn't know you could require one case.Thu, 16 Apr 2009 13:19:12 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspx[quote][b]Jack Corbett (4/16/2009)[/b][hr]Just a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.[/quote]Never thought I would ever have a different opinion than you, but never say never.I strongly advise to move this task to the DB side. Thats why we have constraints within the DB as well. We don't trust the application by definition :-D(It could also be that I misunderstood you and you meant to only validate the UPPER case in the DB, but UPPER the data itself in the application)Thu, 16 Apr 2009 11:57:08 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxThanks a bunch.Thu, 16 Apr 2009 07:48:21 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspx[quote]Do you mind posting the link to this thread. I must have missed it. [/quote]Here you go. Flo stirred up quite a debate by identifying a situation where RBAR using CLR was outperforming the set-based solution.[url=http://www.sqlservercentral.com/Forums/Topic695508-338-1.aspx] Performance issue with tally solution[/url]Thu, 16 Apr 2009 07:45:15 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspx[quote]Somehow users managed to input some of the customer order numbers as lower case [/quote]Looks like they tried and failed, Jack. The counter-argument would be that if that column used an uppercase-only collation (or added UPPER to all procs that do inserts), then it wouldn't matter if the application code was broken. I know scrubbing is more of a load on the SQL server, but enforcing data integrity is a primary role of a database. Thu, 16 Apr 2009 07:42:24 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspx[quote]By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations! [/quote]Do you mind posting the link to this thread. I must have missed it.Thanks.Thu, 16 Apr 2009 07:35:37 GMTJacekOhttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxJust a side note. If case is that important to the application then, in my opinion, the application should be upper-casing the string in the first place. Yeah you can do it in the SQL if you need to, but that should be part of the validation.Thu, 16 Apr 2009 07:30:42 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxYou're welcome, Ron. Hope we didn't overwhelm you with detail. ;-) These sidebar discussions often go into way more minutiae than is required to solve your problem, but sometimes they dredge up some really good information. For one-shot fixes, you may not care whether you fix it with UPPER, REPLACE, STUFF, 'CQS'+SUBSTRING(), or 'CQS'+RIGHT(). But when you get to coding transactions with high volumes, it might be a good thing to know which functions are easier on the CPU. Best of luck to you.Thu, 16 Apr 2009 06:55:01 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxThanks Flo and Bob:The Collate statement allowed me to find the incorrect string and the Update query allowed me to correct the data UPDATE sometableSET someColumn = REPLACE(someColumn,'cqs','CQS')WHERE somecolumn LIKE 'cqs%' Thanks again:-)Thu, 16 Apr 2009 06:48:04 GMTrons-605185http://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxJust share it with the entire thread and I'll read it. Like Jeff, I'm now thinking that I will have to learn to code CLR in self defense. ;-)Wed, 15 Apr 2009 18:23:05 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxHi Bob![quote][b]Bob Hovious (4/15/2009)[/b][hr][quote]Is there a way to update a group of records such as all "cqs%" to become "CQS%"? [/quote]Keeping it simple....[code]UPDATE sometable SET someColumn = REPLACE(someColumn,'cqs','CQS')WHERE somecolumn LIKE 'cqs%'[/code]Now for the time trial results. You won the first round because of the COLLATE in the WHERE clause. That was shrewd because it changed ONLY those rows with a lower case 'cqs'. I would still avoid doing that as a matter of practice, because there might be some mixed case characters lurking in there as well.However, it seems that the REPLACE function is about 10% faster than the UPPER function. I assume that it was quicker to test every character to see if it was a 'c' (and replace only three) faster than it was to run the UPPER conversion on every character.[/quote]Just for fun, a different solution. The test results a bit strange. Sometimes the STUFF seems to be faster than the REPLACE and sometimes other way around. I also tested 'CQS' + SUBSTRING... but seems to be slower. Same with RIGHT...[code]--- resetupdate #testTable set Keyno = lower(keyNo)where keyNo not like '%1%' set statistics time on;print '--Flo 2' UPDATE #testTable SET keyno = STUFF(keyNo, 1, 3, 'CQS') WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'set statistics time off;[/code][quote]Sorry to butt in, Flo, but you're answering ALL the questions!! :-P[/quote]:-DSorry for that, I've not been online the most of the day and just have been a bore.:-D[quote]By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations! :-)[/quote]Yes, it has been a really great discussion! I never saw so much input by so many professionals in any other thread. I'm still doin' some many tests, also for some other CLR approaches. When I'm done I'll share it with you!GreetsFloWed, 15 Apr 2009 17:45:15 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspx[quote]Is there a way to update a group of records such as all "cqs%" to become "CQS%"? [/quote]Keeping it simple....[code]UPDATE sometable SET someColumn = REPLACE(someColumn,'cqs','CQS')WHERE somecolumn LIKE 'cqs%'[/code]Sorry to butt in, Flo, but you're answering ALL the questions!! :-PNow for the time trial results. You won the first round because of the COLLATE in the WHERE clause. That was shrewd because it changed ONLY those rows with a lower case 'cqs'. I would still avoid doing that as a matter of practice, because there might be some mixed case characters lurking in there as well.However, it seems that the REPLACE function is about 10% faster than the UPPER function. I assume that it was quicker to test every character to see if it was a 'c' (and replace only three) faster than it was to run the UPPER conversion on every character. By the way, my compliments on the Tally thread. It has been fascinating to follow. I haven't thought of any alternatives worth adding to the thread, but it has been thought provoking and educational all the same. I haven't seen such substantive debate going on in any forum outside SSC. Congratulations! :-)[code];with Tally (N) AS (SELECT TOP 10000 ROW_NUMBER() over (order by sc1.id) FROM Master.dbo.SysColumns sc1)select 'cqs'+LEFT('00000'+CAST(N as varchar(10)),10) as keynointo #testTablefrom tallycreate unique clustered index #pk_TestTable on #testTable ( keyno )update #testTable set Keyno = UPPER(keyNo)where keyNo like '%1%'set statistics time on;print '--Flo' UPDATE #testTable SET keyno = UPPER(keyNo) FROM #testTable t WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'set statistics time off;-- resetupdate #testTable set Keyno = lower(keyNo)where keyNo not like '%1%'set statistics time on;print '--Bob' UPDATE #testTable SET keyno = REPLACE(keyNo,'cqs','CQS') WHERE keyNo COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%' set statistics time off;drop table #testTable[/code]Wed, 15 Apr 2009 16:25:33 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxHiWhat about UPPER? ;-)[code]DECLARE @t TABLE( txt VARCHAR(30))INSERT INTO @T SELECT 'CQS0002509' UNION ALL SELECT 'CQS0002510' UNION ALL SELECT 'CQS0002511' UNION ALL SELECT 'cqs0002512' UNION ALL SELECT 'cqs0002513' UNION ALL SELECT 'cqs0002514' UNION ALL SELECT 'cqs0002515' UNION ALL SELECT 'CQS0002516' UNION ALL SELECT 'CQS0002517' UNION ALL SELECT 'CQS0002518' UNION ALL SELECT 'CQS0002519' UNION ALL SELECT 'CQS0002520'UPDATE t SET txt = UPPER(txt) FROM @t t WHERE txt COLLATE SQL_Latin1_General_CP1_CS_AS LIKE 'cqs%'SELECT * FROM @t[/code]GreetsFloWed, 15 Apr 2009 15:02:43 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxData in co_num column of customer table shows:CQS0002509CQS0002510CQS0002511cqs0002512cqs0002513cqs0002514cqs0002515CQS0002516CQS0002517CQS0002518CQS0002519CQS0002520Tried the select statement as follows:SELECT * FROM co WHERE co_num COLLATE SQL_Latin1_General_CP1_CS_AS like 'cqs%'Results:co_num----------cqs0002512cqs0002513cqs0002514cqs0002515which is exactly what I want :-)Now I need to fix the data using the following Update commandupdate coset co_num = 'cqs0002515'where co_num = 'CQS0002515'Is there a way to update a group of records such as all "cqs%" to become "CQS%"?Wed, 15 Apr 2009 14:58:25 GMTrons-605185http://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxHiThe answer depends on your future requirements. If it is just a temporary failure you can use COLLATE within the WHERE clause to specify the column as case-sensitive. If this is/becomes a standard requirement you should set the collation of the column to case-sensitive.Here a little sample for both approaches:[code]DECLARE @t TABLE( Id INT, CS_text VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS, CI_text VARCHAR(30))INSERT INTO @t SELECT 1, 'hello', 'world' UNION ALL SELECT 2, 'Hello', 'World'SELECT * FROM @t WHERE CS_text = 'Hello'SELECT * FROM @t WHERE CI_text COLLATE SQL_Latin1_General_CP1_CS_AS = 'World'[/code]GreetsFloWed, 15 Apr 2009 14:14:52 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic697897-338-1.aspxSQL newbie using MS SQL 2005. Have customer order table with customer order numbers that are prefixed with "CQS" followed by a 7 digit number.Example: CQS0002537.Somehow users managed to input some of the customer order numbers as lower case "cqs0002536" instead of the correct upper case CQSxxxxxx.Question: what is the syntax of a sql script/query to search the co_num column in the customers table to show justthe data that is "cqs%" but not return "CQS%".Would also like to know how to do the same for the inverse (show only the upper case "CQS" but not the lower case "cqs").I tried the Upper and Lower functions but it showed all results in either upper/lower case - it did not show the data as it really exists.Looking for best method to search for case sensitive data.Thanks.RonsWed, 15 Apr 2009 14:07:06 GMTrons-605185