Recursive Queries Using Common Table Expressions

Just started playing with recursive Queries in SQL2008. Microsoft has some useful documentation.zzz

List manipulation in SQL

Here are a three functions for list manipulation using SQL.

@RowData is the name of a column.

@SplitOn is the list delimiter (eg comma, period, space etc)

Get the first item:

ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Get the last item:

right(@RowData, charindex('.', reverse(@RowData), 1) - 1)

Get the list without the first item:
Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

These are the equivalent of ColdFusion's ListFirst(), ListLast() and ListRest() functions.

Stripping characters that can cause problems in XML, JSON, etc

Use this code to remove high ascii characters (higher than character 127) and control characters from text.

 REReplace(content,"[^\x20-\x7f]","","all")

Compare two tables in SQL Server 2000

Thanks to Jeff's SQL Server Blog for this excellent code to compare the contents of two tables in SQL

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...

FROM

(

  SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...

  FROM A

  UNION ALL

  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...

  FROM B

) tmp

GROUP BY ID, COL1, COL2, COL3 ...

HAVING COUNT(*) = 1

ORDER BY ID

Cannot resolve collation conflict for equal to operation

I had this error today. A quick Google search found a workaround in this article. Seems adding 'COLLATE DATABASE_DEFAULT' after the field names can fix this problem, eg:

SELECT ID
FROM staff INNER JOIN departments
WHERE staff.ID COLLATE DATABASE_DEFAULT
  = departments.STAFFID COLLATE DATABASE_DEFAULT

Using Java methods in ColdFusion

Because ColdFusion variables map to Java objects it is possible to use Java methods directly on the ColdFusion variables. The methods available depend on the underlying Java data type.

Example:
<cfset string1 = "untitled">
<cfset string2 = string1.substring(2, 7)> produces "title"

Here are some links to the documentation.

http://java.sun.com/j2se/1.4.2/docs/api/java/lang/package-tree.html

http://java.sun.com/j2se/1.4.2/docs/api/java/util/Vector.html

Troubleshooting CFHTTP

Great article on troubleshooting CFHTTP. Helped me fix a connection failure issue that was driving me nuts.