CHARINDEX - Lower Case Issue

[smedvid]

Hi All,
I need to strip out a version number from a string. The version number will always be a lower case "v" with a leading space. Appears that my SQL Server environment is ignoring lower case. I am searching for a setting, bout can't find it. I hope that is the issue; or I will need to have a case with lower case v with 0 to 9 (10 possibilities).

SELECT 
a.TemplateName,
CASE CHARINDEX(' v',a.TemplateName)
  WHEN 0 THEN a.TemplateName
  ELSE SUBSTRING(a.TemplateName,1,CHARINDEX(' v',a.TemplateName)-1) 
END as TemplateNameNoVersion
  FROM dbo.tbl_Data a
  WHERE a.TemplateName Like '% v%' OR a.TemplateName Like '% V%' 
GROUP BY a.TemplateName  
ORDER BY 1

Raw Data
Test Notary v1.1 to Test Notary
Test Sale Valuation to Test Sale Valuation (No Changes)
Test Referral v.1.2 to Test Referral
Test VM to Test VM (No Changes)

Code above works great, except entries like Test VM will return just Test. Basically ignoring the case of the letter "v".

Any thoughts are greatly appreciated. Scratching my head on this one for show.

Thanks in Advance


Steve Medvid
IT Consultant & Web Master

 

[smedvid]

Sometimes the dust clears from the brain after a walk away from the PC, and a fresh look at the issue is taken. I knew that SQL Server Defaults to Upper case, and then recalled you can override within SQL SELECT Statement by listing Collate information, and I have done it before many many years ago. See below for corrected code block, that I hope will help someone in the future with a similar issue of Mixed Case within a CHARINDEX Function or SELECT Statement.

SELECT 
a.TemplateName,
CASE CHARINDEX(' v',a.TemplateName Collate SQL_Latin1_General_CP1_CS_AS)
  WHEN 0 THEN a.TemplateName
  ELSE SUBSTRING(a.TemplateName,1,CHARINDEX(' v',a.TemplateName Collate SQL_Latin1_General_CP1_CS_AS)-1) 
END as TemplateNameNoVersion
  FROM dbo.tbl_Data a
  WHERE a.TemplateName Like '% v%' OR a.TemplateName Like '% V%' 
GROUP BY a.TemplateName  
ORDER BY 1

Notice within the CHARINDEX Function, the specific Collate reference is listed.



Steve Medvid
IT Consultant & Web Master