14 September 2007

Some Stuff Here

    It’s amazing, no matter how much experience we have in a programming language or a technology, there is always something new to learn. There are a few hardly used functions in SQL Server 2000, but they could be really useful in certain situations. One of such functions is Stuff, I discovered its use long time after I started to play with SQL. Stuff ( character_expression , start , length , character_expression ) --inserting a string inside another without doing a replacement SELECT Stuff('This is just a test', 3, 0, 'x') Output: Thxis is just a test --inserting a string inside another without doing a replacement SELECT Stuff('This is just a test', 3, 5, 'at was') Output: That was just a test     So, it could be useful when we check whether a character is on a certain position, and replace it with another character. Normally we would have to write something like: DECLARE @string varchar(50) SET @string = 'DE1988299X8829' SELECT CASE WHEN Substring(@string, 10,1) = 'X' THEN Stuff(@string, 10, 1, 'Y') ELSE @string END Output: DE1988299Y8829     Another function I haven't saw too often in SQL logic is Replicate, yeah, it does exactly what it's name suggests - it takes a string and replicates it's content multiple times. Replicate ( character_expression , integer_expression ) SELECT Replicate ('0', 10) Output: 0000000000 SELECT Replicate ('tone', 3) Output: tonetonetone     The function could be useful when we need to put a number of characters in front of a value. For example a table contains integer values, but in a report we need them with leading zeroes (e.g. 00056 instead of 56). I tried to implement such functionality as a function, in a previous posting with the help of Space function; using Space and Replace functions can be obtained the same result as using Replicate: SELECT Replace(Space(3), ' ', 'tone') Output: tonetonetone

No comments: