 |
Changing columns to rows.
|
| Total Hit (1467) |
Another example that shows how to convert columnar values into individual rows.
|
Rating
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Methods to form complex strings for dynamic SQL execution
|
| Total Hit (1550) |
This T-SQL script shows several techniques to form complex dynamic SQL strings. These eliminate to some extent the coding involved in concatenating string values and making sure to add single quotes appropriately between values. The methods discussed can make the code simpler to read & is less error
....Read More |
| Rating
|
|
|
|
Tricks using a numbers table
|
| Total Hit (1590) |
Solving most problems using a relational technique is more elegant and better in terms of performance. This script shows few examples that uses a Numbers table to solve some common problems. Keep a table with Numbers & it will prove very handy.
|
| Rating
|
|
|
|
|
|
Example for encrypting data using XOR
|
| Total Hit (1836) |
Functions to encrypt data in columns are not available in SQL Server (except undocumented ones that are risky to use & not supported). Here is one that shows how to do this using XOR.
«code LangId=6»DECLARE @pwd varchar(30) , @encryptkey int , @encryptedpwd varchar(30) , @charpos smallint;
SELE
....Read More |
| Rating
|
|
|
|
Check a string for numeric digits only
|
| Total Hit (1807) |
The ISNUMERIC function in SQL60/65/70 checks for decimal & integer values. Hence characters like D, E are valid float representations & similarly ','. This is a simple logic that can check only for numeric digits without using a loop of any kind.
|
| Rating
|
|
|
|
|
|
Strip the tags out of a HTML string
|
| Total Hit (1321) |
A relational technique to strip the HTML tags out of a string. This solution demonstrates how to use simple tables & search functions effectively in SQL Server to solve procedural / iterative problems.
|
| Rating
|
|
|
|
Counting ocurrences of a pattern in text data
|
| Total Hit (1435) |
A sample script to count the number of occurrences of a particular string pattern in a text column. This demonstrates how to use PATINDEX with simple SELECT statements.
|
| Rating
|
|
|
|
|
|
File details using NT commands & undocumented SP.
|
| Total Hit (2290) |
Getting file details is easy using the undocumented extended SP 'xp_getfiledetails'. But you can't rely on this to be the same between versions of SQL Server or even service packs. So here is an alternative method using the standard NT commands. The undocumented SP is also shown for completeness.
....Read More |
| Rating
|
|
|
|
|
|
Finding gaps in sequential numbers.
|
| Total Hit (2825) |
A simple JOIN that will determine gaps in a set of sequential values. This query will basically give the sequence number after which a gap is present.
|
| Rating
|
|
|
|
Dynamic execution of SP ( This method is not well-known ).
|
| Total Hit (2463) |
This method of dynamic SQL execution is not well-known. This will work from SQL60 & upwards. This is very useful when calling SPs dynamically with parameters of different datatypes, output parameters & return value.
|
| Rating
|
|
|
|
|
|
|
|
Maximum Value from 5 columns.
|
| Total Hit (1413) |
This script shows how to obtain the maximum value of 5 columns. It is possible to do this using CASE statement but the expression gets quite lengthy. This shows an easy way to use the MAX function itself & this approach can be extended to as many columns as you want.
....Read More |
| Rating
|
|
|
|
|
|
A tricky ordering problem.
|
| Total Hit (1749) |
This is 2 of the solutions to an ORDER BY problem posed in the microsoft.sqlserver.programming newsgroups. This demonstrates several powerful derived table techniques.
|
| Rating
|
|
|
|
|
|
Generic logic to search & replace characters in a string.
|
| Total Hit (1516) |
A generic logic to search & replace certain characters in a string. This approach is flexible in the sense that new characters can be added for searching without modifying the logic. This will make the code more maintainable too.
|
| Rating
|
|