Welcome to Shivshanker's Blog

Added line is this color

Deleted line is this color

+
+<p> </p>
+<p><span style="color: #cc0000"><span style="font-size: 130%"><font size=
+"4"><span style="color: #990000">BINARY_CHECKSUM<br>
+SIGN<br>
+COLUMNPROPERTY<br>
+DATALENGTH<br>
+ASCII, UNICODE<br>
+NULLIF<br>
+PARSENAME<br>
+STUFF<br>
+REVERSE<br>
+GETUTCDATE</span></font></span></span></p>
+<p><span style="color: #cc0000"><span style="font-size: 130%"><font size=
+"4"><span style="color: #990000"><br>
+<strong>BINARY_CHECKSUM</strong></span><br></font></span></span>BINARY_CHECKSUM
+is handy if you want to check for data differences between 2 rows of data<br>
+<br>
+In order to see what rows are in table 1 and not in table 2 and vice versa you
+can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows
+that are different you can use BINARY_CHECKSUM. You have to run this example o
+SQL Server 2000 to see it work, you can ofcourse use any tables just modify the
+queries<br>
+Let’s get started…<br>
+<br>
+<span style="color: #009900">--let's copy over 20 rows to a table named
+authors2</span><br>
+<span style="color: #3333ff">SELECT TOP</span> 20 * <span style=
+"color: #3333ff">INTO</span> tempdb..authors2<br>
+<span style="color: #3333ff">FROM</span> pubs..authors<br>
+<br>
+<span style="color: #009900">--update 5 records by appending X to the
+au_fname<br></span><span style="color: #3333ff">SET</span> ROWCOUNT 5<br>
+<br>
+<br>
+<span style="color: #3333ff">UPDATE</span> tempdb..authors2<br>
+<span style="color: #3333ff">SET</span> au_fname =au_fname +'X'<br>
+<br>
+<br>
+<span style="color: #009900">--Set rowcount back to 0</span><br>
+<span style="color: #3333ff">SET</span> ROWCOUNT 0<br>
+<br>
+<span style="color: #009900">--let's insert a row that doesn't exist in
+pubs</span><br>
+<span style="color: #3333ff">INSERT INTO</span> tempdb..authors2<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #ff0000">'666-66-6666'</span>, au_lname, au_fname, phone, address,
+city, state, zip, contract<br>
+<span style="color: #3333ff">FROM</span> tempdb..authors2<br>
+<span style="color: #3333ff">WHERE</span> au_id =<span style=
+"color: #ff0000">'172-32-1176'<br></span><br>
+<span style="color: #009900">--*** The BIG SELECT QUERY --***<br>
+<br>
+--Not in Pubs<br></span><span style="color: #3366ff">SELECT</span> <span style=
+"color: #ff0000">'Does Not Exist On Production'</span>,t2.au_id<br>
+<span style="color: #3366ff">FROM</span> pubs..authors t1<br>
+<span style="color: #cc33cc">RIGHT</span> JOIN tempdb..authors2 t2 <span style=
+"color: #3333ff">ON</span> t1.au_id =t2.au_id<br>
+<span style="color: #3333ff">WHERE</span> t1.au_id <span style=
+"color: #3333ff">IS</span> NULL<br>
+<span style="color: #3333ff">UNION</span> ALL<br>
+<span style="color: #009900">--Not in Temp<br></span><span style=
+"color: #3333ff">SELECT</span> <span style="color: #ff0000">'Does Not Exist In
+Staging'</span>,t1.au_id<br>
+<span style="color: #3333ff">FROM</span> pubs..authors t1<br>
+<span style="color: #cc33cc">LEFT</span> JOIN tempdb..authors2 t2 <span style=
+"color: #3333ff">ON</span> t1.au_id =t2.au_id<br>
+<span style="color: #3366ff">WHERE</span> t2.au_id <span style=
+"color: #3333ff">IS</span> NULL<br>
+<span style="color: #3333ff">UNION</span> ALL<br>
+<span style="color: #009900">--Data Mismatch<br></span><span style=
+"color: #3333ff">SELECT</span> <span style="color: #ff0000">'Data
+Mismatch'</span>, t1.au_id<br>
+<span style="color: #3366ff">FROM</span>( <span style=
+"color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">BINARY_CHECKSUM</span>(*) AS CheckSum1 ,au_id <span style=
+"color: #3333ff">FROM</span> pubs..authors) t1<br>
+JOIN(SELECT <span style="color: #cc33cc">BINARY_CHECKSUM</span>(*) AS
+CheckSum2,au_id <span style="color: #3333ff">FROM</span> tempdb..authors2) t2
+<span style="color: #3333ff">ON</span> t1.au_id =t2.au_id<br>
+<span style="color: #3333ff">WHERE</span> CheckSum1 &lt;&gt; CheckSum2<br>
+<br>
+<span style="color: #009900">--Clean up</span><br>
+<span style="color: #3333ff">DROP TABLE</span> tempdb..authors2<br>
+GO<br>
+<br>
+<br>
+<br>
+<br>
+<font size="4"><span style="color: #990000"><span style=
+"font-size: 130%"><strong>SIGN</strong></span></span><br></font> Sometimes you
+are asked by the front-end/middle-tier developers to return a rowcount as well
+with the result set. However the developers want you to return 1 if there are
+rows and 0 if there are none. How do you do such a thing?<br>
+Well I am going to show you two ways. the first way is by using CASE and
+@@ROWCOUNT, the second way is by using the SIGN function<br>
+<br>
+For CASE we will do this<br>
+<br>
+RETURN CASE WHEN @@ROWCOUNT &gt; 0 THEN 1 ELSE 0 END<br>
+<br>
+So that's pretty simple, if @@ROWCOUNT is greater than 0 return 1 for
+everything else return 0<br>
+<br>
+Using the SIGN function is even easier, all you have to do is this<br>
+<br>
+RETURN SIGN(@@ROWCOUNT)<br>
+<br>
+That's all, SIGN Returns the positive (+1), zero (0), or negative (-1) sign of
+the given expression. In this case -1 is not possible but the other two values
+are<br>
+So let's see this in action<br>
+<br>
+<br>
+<span style="color: #3333ff">USE</span> pubs<br>
+GO<br>
+<br>
+<span style="color: #009900">--Case Proc<br></span><span style=
+"color: #3333ff">CREATE PROCEDURE</span> TestReturnValues<br>
+@au_id <span style="color: #3333ff">VARCHAR</span>(49) =<span style=
+"color: #ff0000">'172-32-1176'</span><br>
+<span style="color: #3333ff">AS<br>
+SELECT</span> *<br>
+<span style="color: #3333ff">FROM</span> authors<br>
+<span style="color: #3333ff">WHERE</span> au_id =@au_id<br>
+<br>
+<span style="color: #3333ff">RETURN</span> <span style=
+"color: #cc33cc">CASE</span> <span style="color: #3333ff">WHEN</span>
+<span style="color: #cc33cc">@@ROWCOUNT</span> &gt; 0 <span style=
+"color: #3333ff">THEN</span> 1 <span style="color: #3333ff">ELSE</span> 0
+<span style="color: #3333ff">END</span><br>
+GO<br>
+<br>
+<span style="color: #009900">--Sign Proc</span><br>
+<span style="color: #3333ff">CREATE PROCEDURE</span> TestReturnValues2<br>
+@au_id <span style="color: #3333ff">VARCHAR</span>(49) =<span style=
+"color: #ff0000">'172-32-1176'</span><br>
+<span style="color: #3333ff">AS<br>
+SELECT</span> *<br>
+<span style="color: #3333ff">FROM</span> authors<br>
+<span style="color: #3333ff">WHERE</span> au_id =@au_id<br>
+<br>
+<span style="color: #3333ff">RETURN</span> <span style=
+"color: #cc33cc">SIGN</span>(<span style=
+"color: #cc33cc">@@ROWCOUNT</span>)<br>
+GO<br>
+<br>
+<br>
+<span style="color: #009900">--Case Proc, 1 will be returned; default value is
+used</span><br>
+<span style="color: #3333ff">DECLARE</span> @Rowcount <span style=
+"color: #3333ff">int</span><br>
+<span style="color: #3333ff">EXEC</span> @Rowcount = TestReturnValues<br>
+<span style="color: #3333ff">SELECT</span> @Rowcount<br>
+GO<br>
+<br>
+<span style="color: #009900">--Case Proc, 0 will be returned; dummy value is
+used</span><br>
+<span style="color: #3333ff">DECLARE</span> @Rowcount <span style=
+"color: #3333ff">int</span><br>
+<span style="color: #3333ff">EXEC</span> @Rowcount = TestReturnValues 'ABC'<br>
+<span style="color: #3333ff">SELECT</span> @Rowcount<br>
+GO<br>
+<br>
+<span style="color: #009900">--Sign Proc, 1 will be returned; default value is
+used</span><br>
+<span style="color: #3333ff">DECLARE</span> @Rowcount <span style=
+"color: #3333ff">int</span><br>
+<span style="color: #3333ff">EXEC</span> @Rowcount = TestReturnValues2<br>
+<span style="color: #3333ff">SELECT</span> @Rowcount<br>
+GO<br>
+<br>
+<span style="color: #009900">--Sign Proc, 0 will be returned; dummy value is
+used</span><br>
+<span style="color: #3333ff">DECLARE</span> @Rowcount <span style=
+"color: #3333ff">int</span><br>
+<span style="color: #3333ff">EXEC</span> @Rowcount = TestReturnValues2
+'ABC'<br>
+<span style="color: #3333ff">SELECT</span> @Rowcount<br>
+GO<br>
+<br>
+<br>
+<span style="color: #009900">--Help the environment by recycling ;-)</span><br>
+<span style="color: #3333ff">DROP PROCEDURE</span>
+TestReturnValues2,TestReturnValues<br>
+GO<br>
+<br>
+<br>
+<font size="4"><span style="color: #990000"><span style=
+"font-size: 130%"><strong>COLUMNPROPERTY</strong></span></span><br></font>
+<font size="4"><span style="color: #990000"><span style=
+"font-size: 130%"><span style="color: #000000">COLUMNPROPERTY is handy if you
+need to find scale, precision, if it is an identity column and more. I have
+listed all of them below</span><br></span></span><br></font><span style=
+"color: #3333ff">CREATE TABLE</span> blah (ID <span style=
+"color: #3333ff">DECIMAL</span>(5,2) not null <span style=
+"color: #3333ff">DEFAULT</span> 99)<br>
+<span style="color: #3333ff">INSERT</span> blah <span style=
+"color: #3333ff">DEFAULT VALUES</span><br>
+<br>
+<span style="color: #3333ff">SELECT</span> * <span style=
+"color: #3333ff">FROM</span> blah<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'AllowsNull'</span>)
+<span style="color: #3333ff">AS</span> AllowsNull,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'IsComputed'</span>)
+<span style="color: #3333ff">AS</span> IsComputed,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style=
+"color: #ff0000">'IsCursorType'</span>) <span style="color: #3333ff">AS</span>
+IsCursorType,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style=
+"color: #ff0000">'IsDeterministic'</span>) <span style=
+"color: #3333ff">AS</span> IsDeterministic,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style=
+"color: #ff0000">'IsFulltextIndexed'</span>) <span style=
+"color: #3333ff">AS</span> IsFulltextIndexed,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'IsIdentity'</span>)
+<span style="color: #3333ff">AS</span> IsFulltextIndexed,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style=
+"color: #ff0000">'IsIdNotForRepl'</span>) <span style=
+"color: #3333ff">AS</span> IsIdNotForRepl,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'IsIndexable'</span>)
+<span style="color: #3333ff">AS</span> IsIndexable,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'IsOutParam'</span>)
+<span style="color: #3333ff">AS</span> IsOutParam,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'IsPrecise'</span>)
+<span style="color: #3333ff">AS</span> IsPrecise,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style=
+"color: #ff0000">'IsRowGuidCol'</span>) <span style="color: #3333ff">AS</span>
+IsRowGuidCol,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'Precision'</span>)
+<span style="color: #3333ff">AS</span> <span style=
+"color: #ff0000">'Precision'</span>,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style="color: #ff0000">'Scale'</span>)
+<span style="color: #3333ff">AS</span> Scale,<br>
+<span style="color: #cc33cc">COLUMNPROPERTY</span>( <span style=
+"color: #cc33cc">OBJECT_ID</span>(<span style=
+"color: #ff0000">'blah'</span>),<span style=
+"color: #ff0000">'ID'</span>,<span style=
+"color: #ff0000">'UsesAnsiTrim'</span>) <span style="color: #3333ff">AS</span>
+UsesAnsiTrim<br>
+<span style="color: #3333ff">FROM</span> Blah<br>
+<br>
+<br>
+So what does all that stuff mean?<br>
+<br>
+<strong>AllowsNull</strong><br>
+Allows null values. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsComputed</strong><br>
+The column is a computed column. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsCursorType</strong><br>
+The procedure parameter is of type CURSOR. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsDeterministic</strong><br>
+The column is deterministic. This property applies only to computed columns and
+view columns. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input. Not a computed column or view column.<br>
+<br>
+<strong>IsFulltextIndexed</strong><br>
+The column has been registered for full-text indexing. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsIdentity</strong><br>
+The column uses the IDENTITY property. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsIdNotForRepl</strong><br>
+The column checks for the IDENTITY_INSERT setting. If IDENTITY NOT FOR
+REPLICATION is specified, the IDENTITY_INSERT setting is not checked. 1 =
+TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsIndexable</strong><br>
+The column can be indexed. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsOutParam</strong><br>
+The procedure parameter is an output parameter. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>IsPrecise</strong><br>
+The column is precise. This property applies only to deterministic columns. 1 =
+TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input. Not a deterministic column<br>
+<br>
+<strong>IsRowGuidCol</strong><br>
+The column has the uniqueidentifier data type and is defined with the
+ROWGUIDCOL property. 1 = TRUE<br>
+0 = FALSE<br>
+NULL = Invalid input<br>
+<br>
+<strong>Precision</strong><br>
+Precision for the data type of the column or parameter. The precision of the
+specified column data type<br>
+NULL = Invalid input<br>
+<br>
+<strong>Scale</strong><br>
+Scale for the data type of the column or parameter. The scale<br>
+NULL = Invalid input<br>
+<br>
+<strong>UsesAnsiTrim</strong><br>
+ANSI padding setting was ON when the table was initially created. 1= TRUE<br>
+0= FALSE<br>
+NULL = Invalid input<br>
+<br>
+<br>
+<br>
+<br>
+<font size="4"><span style="color: #990000"><span style=
+"font-size: 130%"><strong>DATALENGTH</strong></span></span><br></font> Okay so
+you know the LEN function but do you know the DATALENGTH function? There are
+two major difference between LEN and DATALENGTH.<br>
+The first one deals with trailing spaces, execute the following code and you
+will see that LEN returns 3 while DATALENGTH returns 4<br>
+<br>
+<span style="color: #3333ff">DECLARE</span> @V <span style=
+"color: #3333ff">VARCHAR</span>(50)<br>
+<span style="color: #3333ff">SELECT</span> @V =<span style=
+"color: #ff0000">'ABC '</span><br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">LEN</span>(@V),<span style=
+"color: #cc33cc">DATALENGTH</span>(@V),@V<br>
+<br>
+The second difference deals with unicode character data, as you know unicode
+uses 2 bytes to store 1 character<br>
+Run the following example and you will see that LEN returns 3 while DATALENGTH
+returns 6<br>
+<span style="color: #3333ff">DECLARE</span> @V <span style=
+"color: #3333ff">NVARCHAR</span>(50)<br>
+<span style="color: #3333ff">SELECT</span> @V =<span style=
+"color: #ff0000">'ABC'</span><br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">LEN</span>(@V),<span style=
+"color: #cc33cc">DATALENGTH</span>(@V),@V<br>
+<br>
+If you do DATALENGTH(CONVERT(VARCHAR,@V)) you will get the same as LEN because
+LEN does a RTRIM and converts to VARCHAR before returning<br>
+<br>
+<br>
+<br>
+<font size="4"><strong><span style="font-size: 130%; color: #990000">ASCII,
+CHAR,UNICODE</span></strong><br></font>ASCII will give you the ascii code for a
+character so for A you will get 65<br>
+CHAR does the reverse of ascii CHAR(65) returns A<br>
+UNICODE will give you the unicode value for a character<br>
+NCHAR will give you the character for a unicode or ascii value<br>
+let's see how this works<br>
+<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">ASCII</span>(<span style=
+"color: #ff0000">'A'</span>),<span style=
+"color: #3333ff">CHAR</span>(65),<span style=
+"color: #3333ff">CHAR</span>(<span style=
+"color: #cc33cc">ASCII</span>(<span style="color: #ff0000">'A'</span>)),<br>
+<span style="color: #cc33cc">UNICODE</span>(N<span style=
+"color: #ff0000">'Λ'</span>),<span style=
+"color: #3333ff">NCHAR</span>(923),<span style=
+"color: #3333ff">NCHAR</span>(<span style=
+"color: #cc33cc">UNICODE</span>(N<span style="color: #ff0000">'Λ'</span>))<br>
+<br>
+<br>
+<br>
+<br>
+<font size="4"><strong><span style=
+"font-size: 130%; color: #990000">NULLIF</span></strong><br></font>NULLIF
+Returns a null value if the two specified expressions are equivalent.<br>
+<br>
+Syntax<br>
+NULLIF ( expression , expression )<br>
+<br>
+<span style="color: #3333ff">DECLARE</span> @v <span style=
+"color: #3333ff">VARCHAR</span>(20)<br>
+<span style="color: #3333ff">SELECT</span> @v = <span style="color: #ff0000">'
+'</span><br>
+<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">NULLIF</span>(@v,<span style="color: #ff0000">' '</span>)<br>
+<br>
+You can combine NULLIF with COALESCE if you want to test for NULLS and Blanks
+for example<br>
+<br>
+<span style="color: #3333ff">DECLARE</span> @v <span style=
+"color: #3333ff">VARCHAR</span>(20)<br>
+<span style="color: #3333ff">SELECT</span> @v = <span style="color: #ff0000">'
+'</span><br>
+<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">COALESCE</span>(<span style=
+"color: #cc33cc">NULLIF</span>(@v,<span style="color: #ff0000">'
+'</span>),<span style="color: #ff0000">'N/A'</span>)<br>
+<br>
+<br>
+Here is another NULLIF example:<br>
+<span style="color: #3333ff">CREATE TABLE</span> Blah (SomeCol <span style=
+"color: #3333ff">VARCHAR</span>(33))<br>
+<br>
+<span style="color: #3333ff">INSERT</span> Blah <span style=
+"color: #3333ff">VALUES</span>(NULL)<br>
+<span style="color: #3333ff">INSERT</span> Blah <span style=
+"color: #3333ff">VALUES</span>(<span style="color: #ff0000">''</span>)<br>
+<span style="color: #3333ff">INSERT</span> Blah <span style=
+"color: #3333ff">VALUES</span>(<span style="color: #ff0000">' '</span>)<br>
+<span style="color: #3333ff">INSERT</span> Blah <span style=
+"color: #3333ff">VALUES</span>(<span style="color: #ff0000">'A'</span>)<br>
+<span style="color: #3333ff">INSERT</span> Blah <span style=
+"color: #3333ff">VALUES</span>(<span style="color: #ff0000">'B B'</span>)<br>
+<br>
+<span style="color: #009900">--Using COALESCE and NULLIF</span><br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">COALESCE</span>(<span style=
+"color: #cc33cc">NULLIF</span>(<span style=
+"color: #cc33cc">RTRIM</span>(SomeCol),<span style="color: #ff0000">'
+'</span>),<span style="color: #ff0000">'N/A'</span>)<br>
+<span style="color: #3333ff">FROM</span> Blah<br>
+<br>
+<br>
+<span style="color: #009900">--Using CASE</span><br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">CASE</span> <span style="color: #3333ff">WHEN</span>
+<span style="color: #cc33cc">RTRIM</span>(SomeCol) = <span style=
+"color: #ff0000">''</span> THEN <span style="color: #ff0000">'N/A'</span><br>
+<span style="color: #3366ff">WHEN</span> <span style=
+"color: #cc33cc">RTRIM</span>(SomeCol) <span style="color: #3333ff">IS</span>
+NULL THEN <span style="color: #ff0000">'N/A'<br></span><span style=
+"color: #3333ff">ELSE</span> SomeCol <span style="color: #3333ff">END</span>
+SomeCol<br>
+<span style="color: #3333ff">FROM</span> Blah<br>
+<br>
+<br>
+Output for both queries<br>
+-----------------------<br>
+N/A<br>
+N/A<br>
+N/A<br>
+A<br>
+B B<br>
+<br>
+<br>
+<font size="4"><span style="color: #990000"><span style=
+"font-size: 130%"><strong>PARSENAME</strong></span></span><br></font> PARSENAME
+retrieves parts of string delimited by dots. It is used to split
+DataBaseServer, DataBaseName, ObjectOwner and ObjectName but you can use it to
+split IP addresses, names etc<br>
+<br>
+<span style="color: #3333ff">DECLARE</span> @ParseString <span style=
+"color: #3333ff">VARCHAR</span>(100)<br>
+<span style="color: #3333ff">SELECT</span> @ParseString = <span style=
+"color: #ff0000">'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'</span><br>
+<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">PARSENAME</span>(@ParseString,4),<br>
+<span style="color: #cc33cc">PARSENAME</span>(@ParseString,3),<br>
+<span style="color: #cc33cc">PARSENAME</span>(@ParseString,2),<br>
+<span style="color: #cc33cc">PARSENAME</span>(@ParseString,1)<br>
+<br>
+<br>
+<span style="color: #3333ff">CREATE TABLE</span> #Test (<br>
+SomeField <span style="color: #3333ff">VARCHAR</span>(49))<br>
+<br>
+<span style="color: #3333ff">INSERT INTO</span> #Test<br>
+<span style="color: #3333ff">VALUES</span> (<span style=
+"color: #ff0000">'aaa-bbbbb'</span>)<br>
+<br>
+<span style="color: #3333ff">INSERT INTO</span> #Test<br>
+<span style="color: #3333ff">VALUES</span> (<span style=
+"color: #ff0000">'ppppp-bbbbb'</span>)<br>
+<br>
+<span style="color: #3333ff">INSERT INTO</span> #Test<br>
+<span style="color: #3333ff">VALUES</span> (<span style=
+"color: #ff0000">'zzzz-xxxxx'</span>)<br>
+<br>
+<span color="#009900">--using PARSENAME</span><br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">PARSENAME</span>(<span style=
+"color: #cc33cc">REPLACE</span>(SomeField,<span style=
+"color: #ff0000">'-'</span>,<span style="color: #ff0000">'.'</span>),2)<br>
+<span style="color: #3366ff">FROM</span> #Test<br>
+<br>
+<br>
+<br>
+Another example:<br>
+<br>
+<span style="color: #3333ff">CREATE TABLE</span> BadData (FullName <span style=
+"color: #3333ff">varchar</span>(20) NOT NULL);<br>
+<span style="color: #3333ff">INSERT INTO</span> BadData (FullName)<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #ff0000">'Clinton, Bill'</span> <span style=
+"color: #3333ff">UNION</span> ALL<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #ff0000">'Johnson, Lyndon, B.'</span> <span style=
+"color: #3333ff">UNION</span> ALL<br>
+<span style="color: #3333ff">SELECT</span> <span style="color: #ff0000">'Bush,
+George, H.W.'</span>;<br>
+<br>
+Split the names into 3 columns<br>
+<br>
+Your output should be this:<br>
+<strong>LastName FirstName MiddleInitial</strong><br>
+Clinton Bill<br>
+Johnson Lyndon B.<br>
+Bush George H.W.<br>
+<br>
+<span style="color: #3333ff">SELECT</span> FullName,<span style=
+"color: #cc33cc">PARSENAME</span>(FullName2,NameLen+1) <span style=
+"color: #3333ff">AS</span> LastName,<br>
+<span style="color: #cc33cc">PARSENAME</span>(FullName2,NameLen) <span style=
+"color: #3333ff">AS</span> FirstName,<br>
+<span style="color: #cc33cc">COALESCE</span>(<span style=
+"color: #cc33cc">REPLACE</span>(<span style=
+"color: #cc33cc">PARSENAME</span>(FullName2,NameLen-1),<span style=
+"color: #ff0000">'~'</span>,<span style="color: #ff0000">'.'</span>),'')
+<span style="color: #3333ff">AS</span> MiddleInitial<br>
+<span style="color: #3333ff">FROM</span>(<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">LEN</span>(FullName) -<span style=
+"color: #cc33cc">LEN</span>(<span style=
+"color: #cc33cc">REPLACE</span>(FullName,',','')) <span style=
+"color: #3333ff">AS</span> NameLen,<br>
+<span style="color: #cc33cc">REPLACE</span>(<span style=
+"color: #cc33cc">REPLACE</span>(FullName,<span style=
+"color: #ff0000">'.'</span>,<span style=
+"color: #ff0000">'~'</span>),<span style="color: #ff0000">',
+'</span>,<span style="color: #ff0000">'.'</span>) <span style=
+"color: #3333ff">AS</span> FullName2,FullName<br>
+<span style="color: #3333ff">FROM</span> BadData) x<br>
+<br>
+<br>
+<br>
+<font size="4"><strong><span style=
+"font-size: 130%; color: #990000">STUFF</span></strong><br></font>STUFF is
+another function that is hardly used, it is useful if you want to replace or
+add characters inside data<br>
+Take a look at the code below. the first STUFF will replace X with 98765, the
+second STUFF will place 98765 before the X and the third stuff will replace X-
+with 98765<br>
+<span style="color: #3333ff">DECLARE</span> @v <span style=
+"color: #3333ff">VARCHAR</span>(11)<br>
+<span style="color: #3333ff">SELECT</span> @v =<span style=
+"color: #ff0000">'-X-'</span><br>
+<br>
+<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">STUFF</span>(@v, 2, 1, <span style=
+"color: #ff0000">'98765'</span>),<br>
+<span style="color: #cc33cc">STUFF</span>(@v, 2, 0, <span style=
+"color: #ff0000">'98765'</span>),<br>
+<span style="color: #cc33cc">STUFF</span>(@v, 2, 2, <span style=
+"color: #ff0000">'98765'</span>)<br>
+<br>
+<br>
+The STUFF function is very handy if you need to insert dashes in a social
+security. You can accomplish that by using the function STUFF twice instead of
+using substring,left and right<br>
+<br>
+<span style="color: #3333ff">DECLARE</span> @v <span style=
+"color: #3333ff">VARCHAR</span>(11)<br>
+<span style="color: #3333ff">SELECT</span> @v =<span style=
+"color: #ff0000">'123456789'</span><br>
+<br>
+<span style="color: #3333ff">SELECT</span> @v,<span style=
+"color: #cc33cc">STUFF</span>(<span style=
+"color: #cc33cc">STUFF</span>(@v,4,0,<span style=
+"color: #ff0000">'-'</span>),7,0,<span style="color: #ff0000">'-'</span>)<br>
+<br>
+<br>
+<br>
+<span style="font-size: 130%"><span style="color: #990000"><font size=
+"4"><strong>REVERSE</strong><br></font></span></span>REVERSE just reverses the
+value, for example the code below returns CBA<br>
+<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">REVERSE</span>(<span style="color: #ff0000">'ABC'</span>)<br>
+<br>
+Reverse is handy if you need to split values, take a look at this example<br>
+<br>
+<span style="color: #3333ff">CREATE TABLE</span> #TestCityStateZip (csz
+<span style="color: #3333ff">CHAR</span>(49))<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'city ,st
+12223'</span>)<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'New York City,NY
+10028'</span>)<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'Princeton , NJ
+08536'</span>)<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'Princeton,NJ
+08536 '</span>)<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'Long Island City,
+NY 10013'</span>)<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'Long Island City,
+NY 10013 '</span>)<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'Long Island City
+, NY 10013'</span>)<br>
+<span style="color: #3333ff">INSERT INTO</span> #TestCityStateZip <span style=
+"color: #3333ff">VALUES</span> (<span style="color: #ff0000">'Long Island City
+,NY 10013 '</span>)<br>
+<br>
+<br>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">LEFT</span>(csz,<span style=
+"color: #cc33cc">CHARINDEX</span>(<span style=
+"color: #ff0000">','</span>,csz)-1)<span style="color: #3333ff">AS</span>
+City,<br>
+<span style="color: #cc33cc">LEFT</span>(<span style=
+"color: #cc33cc">LTRIM</span>(<span style=
+"color: #cc33cc">SUBSTRING</span>(csz,(<span style=
+"color: #cc33cc">CHARINDEX</span>(<span style=
+"color: #ff0000">','</span>,csz)+1),4)),2) <span style=
+"color: #3333ff">AS</span> State,<br>
+<span style="color: #cc33cc">RIGHT</span>(<span style=
+"color: #cc33cc">RTRIM</span>(csz),<span style=
+"color: #cc33cc">CHARINDEX</span>(<span style="color: #ff0000">'
+',</span><span style="color: #cc33cc">REVERSE</span>(<span style=
+"color: #cc33cc">RTRIM</span>(csz)))-1) <span style="color: #3366ff">AS</span>
+Zip<br>
+<span style="color: #3333ff">FROM</span> #TestCityStateZip<br>
+<br>
+<br>
+<br>
+<br>
+<br>
+<font size="4"><span style="color: #990000"><span style=
+"font-size: 130%"><strong>GETUTCDATE</strong></span></span><br></font>
+<span style="color: #3333ff">SELECT</span> <span style=
+"color: #cc33cc">GETUTCDATE</span>()<br>
+<br>
+Returns the datetime value representing the current UTC time (Universal Time
+Coordinate or Greenwich Mean Time). The current UTC time is derived from the
+current local time and the time zone setting in the operating system of the
+computer on which SQL Server is running.</p>