String Comparison Tips From Readers

SSWUG TV
SQL Server Injection prevention and help, interview with Philip Japikse, Patterns and Practices Evangelist for Telerik. We also have the latest news on Oracle’s purchase of RightNow Technologies, IBMs push into unstructured data analysis and more.
Watch the Show

Featured Article(s)
Optimistic Locking in DB2 for z/OS
As of DB2 Version 9, a new technique called "optimistic locking" is available to help reduce lock contention.

Featured White Paper(s)
How to Implement an Effective SharePoint Governance Plan
Written by AvePoint

String Comparison Tips From Readers
A number of people have written in further fleshing out how to use the different string operations I shared with you in the Editorial from 11/29/2011.

Here are a few of the methods I received…

Kaushal demonstrates a number of techniques using LIKE or IN Operations:

SELECT NAME1 FROM TBLNAME WHERE NAME1='ADITI'

SELECT NAME1 from TBLNAME WHERE NAME1 LIKE '%ADITI%'

SELECT NAME1 from TBLNAME WHERE NAME1 LIKE '%_ADITI_%'

SELECT NAME1 from TBLNAME WHERE NAME1 LIKE '%[OO]_%'
SELECT * FROM TBLNAME

INSERT INTO TBLNAME SELECT('KMAUSHALFGYHGFR')
SELECT * FROM TBLNAME
SELECT * FROM TBLNAME WHERE NAME1 IN('KAUSHAL','ADITI', 'KHUSHBOO')

SELECT NAME1 FROM TBLNAME WHERE NAME1 LIKE 'K%[AU]%_L'

THIS QUERY SEARCHS BETWEEN START TO END WITHIN A STRING.

SELECT NAME1 FROM TBLNAME WHERE NAME1 LIKE 'K_____L'
THIS QUERY SEARCHS BETWEEN START TO END WITHIN A STRING.

Erik demonstrates techniques for gathering the contents of other rows before or after a row matching your query exactly. I think there is a bug with the rows < ‘Nice’ because it won’t return the rows nearest to ‘Nice’…but the concept is awesome:

We have an application where we need to essentially create a view on an index, showing X number of records above and below the record that most accurately matches the criteria. To do this, we do a top X/2 where text >= ‘Nice’ and a top X/2 where text < ‘Nice’.

Bob goes over the top. To be honest, I haven’t taken the time to understand completely how his code works. But the things he says it can do are really cool.

Nice article, but I thought I’d throw in my 2c with some interesting string comparison challenges I’ve recently written some code for. Specifically, this is where you’re using a pattern match either with the PATINDEX function or the LIKE operator. These snippets (and similar) have been useful to me in a variety of data validation and parsing scenarios.

This one will find the first upper-case character in a string (it returns "5"):

declare @string varchar(64) = 'ben Taylor'
select patindex('%[A-Z]%',@string collate Latin1_General_BIN)

This one will validate the format of a string. As shown it will return "Valid". Change the "8" to an "A" and you get "Invalid":

declare @string varchar(64) = '03AB228'
select case when @string like '[0-9][0-9][A-Z][A-Z][0-9][0-9][0-9]'

then 'Valid' else 'Invalid'
end

And finally, a snippet that I thought was unique and that I had a lot of fun writing. This will take a string that is either camel-cased or underscore-delimited (which describes the naming convention for object names in most databases) and will convert that into a proper-cased, space-delimited string that might serve for something like a report caption. It uses a combination of explicit collation casts, pattern matching, a recursive CTE, XML and some dynamic TSQL to get the job done, all with no cursor or WHILE loop (the CTE actually takes care of iterating the characters of the string):

declare
@obj_name varchar(256) = 'FourthQuarterProfitAndLossReport'
, @get_rpt_caption_cmd nvarchar(1024)
, @rpt_caption varchar(256)
if charindex('_',@obj_name)>0 or patindex('%[A-Z]%',
substring(@obj_name,2,128)

collate Latin1_General_BIN)>1
begin
;with rt as (
select
@obj_name obj_name
, 1 char_index
, case when substring(@obj_name,1,1) = '_' then 1 else 0
end is_underscore
, 0 is_upper
union all
select
rt.obj_name
, rt.char_index+1
, case when substring(rt.obj_name,rt.char_index+1,1) = '_' then 1 else 0 end is_underscore
, case when substring(rt.obj_name,rt.char_index+1,1) like '[A-Z]' collate
Latin1_General_BIN and
substring(rt.obj_name,rt.char_index,1)<>'_' then 1 else 0 end is_upper
from
rt
where
rt.char_index<=len(rt.obj_name)
)
--select * from rt
select @get_rpt_caption_cmd = N'set @rtn = ltrim(replace('+
(select 'stuff(' [text()] from rt where is_underscore = 1 or is_upper = 1
for xml path(''), elements)+
(select top 1 ''''+obj_name+'''' from rt)+
case when exists(select null from rt where is_underscore = 1) then
(select top 100 percent ','+convert(varchar,char_index+1)+',1,
upper(substring('''+obj_name+''','+
convert(varchar,char_index+1)+',1)))' [text()]
from rt where is_underscore = 1
order by char_index desc for xml path(''), elements)
else '' end+
case when exists(select null from rt where is_upper = 1) then
(select top 100 percent ','+convert(varchar,char_index)+',0,'' '')' [text()]
from rt where is_upper = 1
order by char_index desc
for xml path(''), elements)
else '' end+',''_'','' ''))'

--print @get_rpt_caption_cmd
exec sp_executesql @get_rpt_caption_cmd,N'@rtn varchar(128) out', @rpt_caption out
if left(@rpt_caption,1) not like '[A-Z]' collate Latin1_General_BIN
set @rpt_caption = upper(left(@rpt_caption,1))+
substring(@rpt_caption,2,128)
end
else
begin
set @rpt_caption = @obj_name
end
print @rpt_caption

If you set the @obj_name variable to either ‘FourthQuarterProfitAndLossReport’ or ‘fourth_quarter_profit_and_loss_report’ you’ll get "Fourth Quarter Profit And Loss Report"

I hope you find all this interesting and/or amusing…

Thanks for all the great feedback on Version Does Matter. I’ll have some reader responses on that next Monday. In the meantime, please send an e-mail to btaylor@sswug.org if you would like to share a comment or suggestion.

Cheers,

Ben