|
|
 |
Example for encrypting data using XOR
|
| Total Hit (1841) |
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 (1809) |
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 (1322) |
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 (1437) |
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 (2292) |
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 (2826) |
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 (2466) |
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 (1416) |
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 (1519) |
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
|
|
|
|
Changing columns to rows.
|
| Total Hit (1469) |
Another example that shows how to convert columnar values into individual rows.
|
| Rating
|
|
|
|
|
|
Generate sequence numbers within a group of values.
|
| Total Hit (1467) |
This example shows how to generate sequence numbers within a group of values in a SELECT statement. With proper indexes & search conditions, the example using the SELECT statement with GROUP BY will be very efficient.
|
| Rating
|
|
|
|
Wrong usage of UPDATE FROM clause.
|
| Total Hit (1523) |
This script demonstrates the wrong usage of UPDATE statement with a FROM clause esp. in case of a table with 1-to-many relationship with another tables(s).
|
| Rating
|
|
|
|
|
|
Flight Schedule Ordering problem.
|
| Total Hit (1697) |
A flight schedule ordering problem. This demonstrates the powerful derived table feature. More details regarding the problem is in the link.
|
| Rating
|
|
|
|
Generate combinations of data from columns.
|
| Total Hit (2189) |
How can you generate various combinations of data from existing columns? This method shows one approach using the CUBE operator with the SELECT statement.
|
| Rating
|
|
|
|
|
|
|
|
|
|
|
|