Posts

Showing posts with the label String

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...

TSQL STUFF with XML PATH: Delimit a Series of Strings

Image
Last post, we explored some useful cases where STUFF function can be used. This post we'll take a step further by combining STUFF with XML PATH to combine rows to a single string and separate the values with commas. Let's remind ourselves the STUFF function syntax: STUFF ( original_string, start_position , length , replaceWith_string )  /**************************************************************/  /* Delimit a string of text. Insert a comma between strings.*/ /**************************************************************/  DECLARE @LogicalQueryProccessingOrder TABLE ( phase VARCHAR(10) ); INSERT INTO @LogicalQueryProccessingOrder  VALUES ('FROM'), ('WHERE'), ('GROUP BY'), ('HAVING'), ('SELECT'), ('ORDER BY'); /* this doesn't work because result has an extra comma at the end. Note this return in xml format */ SELECT phase + ', ' FROM @LogicalQueryProccessingOrder FOR XML PATH(...

TSQL String Concatenation with NULL and CONCAT Function

Image
String Concatenation with  plus (+) operator Example: the result of this query will return NULL because @mname is NULL.  Note: NULL mark represent missing data. Thus known data + missing data = unknown DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20); SELECT @lname + ', ' + @fname + ' ' + @mname AS fullName; Example: this query result an error because can't convert 'x, y' string to an TINYINT. DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT = 2; SELECT@lname + ', ' + @fname + ' ' + @a_number AS fullName_and_number; Result: Conversion failed when converting the varchar value 'y, x ' to data type tinyint. To avoid the error above we can convert number 2 to a string like this: DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT ...