About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Friday, September 14, 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: