Posts

Showing posts with the label Regex

Character Search With Case Sensitive

Image
Yesterday, one of my co-workers asked how to search for a character pattern within SQL Server with  case sensitive. That mean 'Adam' != 'adam' I didn't know how. So, here's what I come up with after reading SQL Sever book online. -- Return the string if string start with 'Adam', but not 'adam' DECLARE @str VARCHAR(50) = 'Adam Smith'; --using COLLATE clause with case sensitive collation SELECT @str AS CollateResult WHERE @str like 'Adam%' COLLATE Latin1_General_CS_AI; --using binary_checksum function SELECT @str AS ChecksumResult WHERE BINARY_CHECKSUM(SUBSTRING(@str, 1, 4)) = BINARY_CHECKSUM('Adam'); --Using Like keyword. This doesn't work because like can't differentiate -- between upper and lower case with in a case insensitive SQL Server Instance. SELECT @str AS LikeResult1 WHERE @str like 'adam%'; SELECT @str AS LikeResult2 WHERE @str like '[a]dam%'; Anyway, I learned something ne...

Character Replacement With Regular Expression Range

Image
I was working on a project at work, and came up with the 2 scripts below. Some people may find the scripts useful. The first sql script will replace a range of characters within a single string. The second script will replace a range of characters within a rowset. Real life use of these scripts example: Let's assume that we have an application(or a spreadsheet) tracking people annual income but the income column data type is varchar or text, and users may enter these: 100000.00 $100000 $100000 $100000/yr $ 100000/annual If we try to convert the values above(except the first one) to a decimal or money, the conversion will fail. Our goal is to remove all character accept character 0 to 9 and the period(.). After that we can convert it to a different data type such decimal or money. Notice that I use the same technique on the example in this old post, Replace Function with Recursive CTE --Script 1: replace a range of characters for a single string. DECLARE @original_s...