I often find myself looking for a specific database column that I have no idea where to find. Pouring over hundreds of tables is painful. One way to quickly narrow the search is to use this query...

   1  
   2  SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'THE_COLUMN_NAME' )

...or, if you're unsure exactly what the column is named, but you suspect you know part of the name, then try...

   1  
   2  SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like '%PART_OF_NAME%' )

This procedure generates a 'cross-tab' (or 'pivot table') result set from (almost) any query, even a stored procedure and optionally allows you to specify a column sort order.

The procedure makes extensive use dynamic SQL and hence 'sp_executesql'. Because the scope of temporary tables does not extend outside of an sp_executesql transaction, I've had to use global temporary tables, which means that this procedure could become a bottleneck if used too frequently by multiple sessions.

This stored procedure is provided 'as is' and without any warranty - I've only tested it on a relatively small number of queries. For more information (and my contact details) see http://nwsoft.blogspot.com/2006/05/generic-cross-tab-pivot-table-query.html

This work is licensed under a Creative Commons Attribution 2.5 License.

   1  
   2  CREATE PROCEDURE [dbo].[genericCrosstab]
   3  
   4   -- @tableSpec
   5   --   A comma delimited list of columns with datatypes (and null constraints, if required)
   6   --   e.g. 'col1 INT, col2 VARCHAR(255) NULL'
   7   --   N.B. You only need provide this list if the source of the data is a stored procedure
   8   @tableSpec    NVARCHAR(4000) = '',
   9  
  10   -- @sqlSelect
  11   --   The SELECT clause (or 'EXEC <stored_procedure>' statement) for the input data
  12   --   e.g. 'SELECT a.au_id, a.au_fname, a.au_lname, a.phone, LEFT(t.title, 10) AS [title], LEFT(s.stor_name, 10) AS [store], sales.qty'
  13   --   e.g. 'EXEC dbo.myStoredProcedure <param1>, <param2>'
  14   @sqlSelect    NVARCHAR(512),
  15  
  16   -- @sqlFromWhere
  17   --   The FROM and WHERE clauses for the input data (if a SELECT statement)
  18   --   e.g. 'FROM authors a, titleauthor ta, titles t, stores s, sales WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title_id = sales.title_id AND s.stor_id = sales.stor_id'
  19   --   N.B. Leave this blank if the source of the data is a stored procedure
  20   @sqlFromWhere NVARCHAR(512)  = '',
  21  
  22   -- @pivotExpr
  23   --   The column name or expression to use as the pivot (specifies which values are the column headings for the crosstabbed data)
  24   --   e.g. 'store' or '''Store: '' + [store]'
  25   @pivotExpr    NVARCHAR(255),
  26  
  27   -- @valueExpr
  28   --   A column name or expression to use as the values in the crosstabbed data
  29   --   e.g. 'qty'
  30   @valueExpr    NVARCHAR(512),
  31  
  32   -- @function
  33   --   The aggregation function to use to combine values in the crosstabbed data
  34   --   e.g. 'MIN'
  35   --   N.B. If this parameter is left blank, the procedure will count up occurences and create additional column headings as necessary, e.g. '[X], [Y], [Y (2)], [Z]' if there are two occurences for label 'Y'
  36   @function     NVARCHAR(20)   = '',
  37  
  38   -- @groupBy
  39   --   The column list to group by (these columns will appear to the left of the crosstabbed data)
  40   --   e.g. 'au_id, au_fname, au_lname, phone'
  41   @groupBy      NVARCHAR(512),
  42  
  43   -- @sortLookup
  44   --   A table or view which can be queried to provide a sort order for the columns in the crosstabbed data
  45   --   e.g. 'days_of_the_week'
  46   --   N.B. The specified table or view must have 'label' and 'sort_order' columns
  47   --   N.B. If column order is irrelevant, leave this parameter blank
  48   --   N.B. Feature: if @sortLookup is given as '*', the columns are placed in ascending alphabetical order
  49   @sortLookup   NVARCHAR(40)   = '',
  50  
  51   -- @collation
  52   --   The collation sequence
  53   --   e.g. 'Latin1_general_CI_AS'
  54   @collation   NVARCHAR(40)   = 'Latin1_general_CI_AS',
  55  
  56   -- @verbose
  57   --   Set to 1 if you want debug messages
  58   @verbose     BIT            = 0
  59  
  60  AS
  61  BEGIN
  62  
  63   -- Check that the mandatory parameters are not empty strings
  64   IF LEN(RTRIM(ISNULL(@sqlSelect, ''))) = 0
  65   BEGIN
  66     RAISERROR ('The @sqlSelect parameter cannot be an empty string.', 16, 1)
  67     ROLLBACK TRANSACTION
  68   END
  69  
  70   IF LEN(RTRIM(ISNULL(@pivotExpr, ''))) = 0
  71   BEGIN
  72     RAISERROR ('The @pivotExpr parameter cannot be an empty string.', 16, 1)
  73     ROLLBACK TRANSACTION
  74   END
  75  
  76   IF LEN(RTRIM(ISNULL(@valueExpr, ''))) = 0
  77   BEGIN
  78     RAISERROR ('The @valueExpr parameter cannot be an empty string.', 16, 1)
  79     ROLLBACK TRANSACTION
  80   END
  81  
  82   IF LEN(RTRIM(ISNULL(@groupBy, ''))) = 0
  83   BEGIN
  84     RAISERROR ('The @groupBy parameter cannot be an empty string.', 16, 1)
  85     ROLLBACK TRANSACTION
  86   END
  87  
  88   -- Local variables
  89   DECLARE @i      INTEGER
  90   DECLARE @sql    NVARCHAR(4000)
  91   DECLARE @sqlX   NVARCHAR(4000)
  92   DECLARE @col    NVARCHAR(4000)
  93   DECLARE @pivot  NVARCHAR(4000)
  94   DECLARE @indx   NVARCHAR(10)
  95   DECLARE @cols   NVARCHAR(4000)
  96   DECLARE @where  NVARCHAR(4000)
  97   DECLARE @update NVARCHAR(4000)
  98   DECLARE @value  NVARCHAR(532)
  99   DECLARE @select NVARCHAR(4000)
 100  
 101   -- Drop the global temporary tables we will use (if they already exist)
 102   -- N.B. we have to use global temp tables, as local temp tables are dropped at the end of a batch (so are not accessible outside of the sp_executesql context)
 103   -- This syntax is a bit long winded because I couldn't make the substitution syntax for sp_executesql work (for this example only! - a bug in T-SQL?)
 104   SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
 105   SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
 106   EXEC sp_executesql @sqlX
 107   SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
 108   EXEC sp_executesql @sqlX
 109   SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
 110   EXEC sp_executesql @sqlX
 111   SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
 112   EXEC sp_executesql @sqlX
 113   SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
 114   EXEC sp_executesql @sqlX
 115   SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
 116   EXEC sp_executesql @sqlX
 117  
 118   IF @verbose = 1 SELECT 'Global temporary tables dropped (if present).' AS [Message]
 119  
 120   -- * The real work starts here
 121  
 122   -- Store the input dataset
 123   IF LEN(RTRIM(@tableSpec)) = 0
 124   BEGIN
 125     -- Create and populate the temporary table in one step
 126     SELECT @sql = @sqlSelect + ' INTO ##input ' + @sqlFromWhere
 127     IF @verbose = 1 SELECT @sql AS [Create and populate the temporary table in one step]
 128     EXEC sp_executesql @sql
 129   END
 130   ELSE
 131   BEGIN
 132     -- Create the temporary table first (the only method that can be used if the data is coming from a stored proc)
 133     SELECT @sql = 'CREATE TABLE ##input (' + @tableSpec + ')'
 134     IF @verbose = 1 SELECT @sql AS [Create the temporary table]
 135     EXEC sp_executesql @sql
 136     -- Populate it
 137     SELECT @sql = 'INSERT INTO ##input ' + @sqlSelect + ' ' + @sqlFromWhere
 138     IF @verbose = 1 SELECT @sql AS [Populate the temporary table]
 139     EXEC sp_executesql @sql
 140   END
 141   IF @verbose = 1 SELECT * FROM ##input
 142  
 143   -- Does the query we want to cross-tab already have a column called 'crossTabRowID'? This is a reserved column name!
 144   IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.id = c.id AND o.name = '##input' AND c.name = 'crossTabRowID')
 145   BEGIN
 146     RAISERROR ('The query passed to the [genericCrosstab] procedure uses a reserved column name (''crossTabRowID'').', 16, 1)
 147     ROLLBACK TRANSACTION
 148   END
 149  
 150   -- Add an identity column (we need a 'row ID')
 151   ALTER TABLE ##input ADD crossTabRowID NUMERIC(9, 0) IDENTITY NOT NULL
 152  
 153   -- Generate the column list
 154   SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##colList FROM ##input WHERE 1 = 0'
 155   IF @verbose = 1 SELECT @sql AS [Generate the column list]
 156   EXEC sp_executesql @sql
 157   IF @verbose = 1 SELECT * FROM ##colList
 158  
 159   -- If no aggregation function is specified, we have to check first that there are no duplicates, and - if there are - we need to handle them
 160   IF RTRIM(ISNULL(@function, '')) = ''
 161   BEGIN
 162     SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], COUNT(1) AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
 163   END
 164   ELSE
 165   BEGIN
 166     SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], 1 AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
 167   END
 168   IF @verbose = 1 SELECT @sql AS [Create the ##grpCount table]
 169   EXEC sp_executesql @sql
 170   IF @verbose = 1 SELECT * FROM ##grpCount
 171  
 172   -- Create a temporary table that will act as a lookup (containing all of the non-pivot / non-group columns names)
 173   SELECT [pivot], [count] AS [index], [pivot] AS [column_name] INTO ##lookup FROM ##grpCount WHERE 1 = 0
 174  
 175   -- Build the results table; one row per group
 176   SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##results FROM ##grpCount GROUP BY ' + @groupBy
 177   IF @verbose = 1 SELECT @sql AS [Create the ##results table]
 178   EXEC sp_executesql @sql
 179   IF @verbose = 1 SELECT * FROM ##results
 180  
 181   -- Build the column list, taking into account duplicate occurences of pivotal values
 182   DECLARE xcursor CURSOR FOR SELECT [pivot], MAX([count]) FROM ##grpCount GROUP BY [pivot]
 183   OPEN xcursor
 184  
 185   FETCH NEXT FROM xcursor INTO @pivot, @indx
 186  
 187   WHILE @@FETCH_STATUS = 0
 188   BEGIN
 189  
 190    SELECT @i = 1
 191  
 192    -- Loop over indx
 193    WHILE @i <= @indx
 194    BEGIN
 195  
 196      -- Build the column list
 197      SELECT @col = CASE @i WHEN 1 THEN @pivot ELSE @pivot + ' (' + CAST(@i AS VARCHAR(10)) + ')' END
 198      INSERT INTO ##lookup VALUES (@pivot, @i, @col)
 199      SELECT @col = '[' + @col + '] NVARCHAR(255) NULL'
 200      SELECT @cols =  ISNULL(@cols + ', ', '') + @col
 201  
 202      -- Add the column to the results table
 203      SELECT @sql = 'ALTER TABLE ##results ADD ' + @col
 204      IF @verbose = 1 SELECT @sql AS [Add column to the ##results table]
 205      EXEC sp_executesql @sql
 206  
 207      -- Continue
 208      SELECT @i = @i + 1
 209  
 210    END
 211    
 212    FETCH NEXT FROM xcursor INTO @pivot, @indx
 213  
 214   END
 215  
 216   CLOSE xcursor
 217   DEALLOCATE xcursor 
 218  
 219   IF @verbose = 1 SELECT * FROM ##lookup
 220   IF @verbose = 1 SELECT * FROM ##results
 221  
 222   -- Loop over the column list (using the syscolumns table in the temp database) to build the WHERE clause
 223   DECLARE xcursor CURSOR FOR SELECT DISTINCT c.[colid], c.[name] FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.[id] = c.[id] AND o.[name] = '##colList' ORDER BY c.[colid]
 224   OPEN xcursor
 225  
 226   FETCH NEXT FROM xcursor INTO @indx, @col
 227  
 228   WHILE @@FETCH_STATUS = 0
 229   BEGIN
 230    
 231     -- Build the WHERE clause
 232     SELECT @where = ISNULL(@where + ' AND ', '') + 'ISNULL(t1.[' + @col + '], '''') = ISNULL(t3.[' + @col + '], '''')'
 233    
 234     FETCH NEXT FROM xcursor INTO @indx, @col
 235  
 236   END
 237  
 238   CLOSE xcursor
 239   DEALLOCATE xcursor 
 240  
 241   IF @verbose = 1 SELECT @where AS [WHERE clause]
 242  
 243   -- Create a temp table that will help us build the UPDATE statements to set the values in the pivot table, and the SELECT statement (with columns in preferred order) afterwards
 244   IF RTRIM(ISNULL(@sortLookup, '')) = ''
 245   BEGIN
 246  
 247     -- If no sorting table was specified, just select all columns
 248     SELECT @select = '*'
 249  
 250     -- Sort alphabetically
 251     SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5 WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY c.[name], t5.[index]'
 252  
 253   END
 254   ELSE
 255   BEGIN
 256  
 257     -- The 'group by' column(s) always come(s) first
 258     SELECT @select = @groupBy
 259  
 260     -- Sort in proscribed order
 261     SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot], l.[sort_order] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5, ' + @sortLookup + ' l WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND t5.[pivot] COLLATE ' + @collation + ' *= l.[label] COLLATE ' + @collation + ' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY l.[sort_order], c.[name], t5.[index]'
 262  
 263   END
 264  
 265   IF @verbose = 1 SELECT @sql AS [Build the ##temp table]
 266   EXEC sp_executesql @sql 
 267   IF @verbose = 1 SELECT * FROM ##temp
 268  
 269   -- The value expression
 270   SELECT @value = @valueExpr
 271  
 272   -- If a function has been specified
 273   IF RTRIM(ISNULL(@function, '')) != ''
 274   BEGIN
 275     SELECT @value = @function + '(' + @value + ')'
 276   END
 277  
 278   DECLARE xcursor CURSOR FOR SELECT [index], [name], [pivot] FROM ##temp
 279   OPEN xcursor
 280  
 281   FETCH NEXT FROM xcursor INTO @indx, @col, @pivot
 282  
 283   WHILE @@FETCH_STATUS = 0
 284   BEGIN
 285  
 286    -- Build the SELECT expression
 287    IF @select != '*' SELECT @select = @select + ', ' + @col
 288  
 289    -- Create the SET clause of the UPDATE sql
 290    IF RTRIM(ISNULL(@function, '')) = ''
 291    BEGIN
 292      -- No function specified
 293      SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID = (SELECT MIN(t0.crossTabRowID) FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + ') + (' + CAST(@indx AS VARCHAR(3)) + ' - 1))'
 294    END
 295    ELSE
 296    BEGIN
 297      -- Function specified
 298      SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID IN (SELECT t0.crossTabRowID FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + '))'
 299    END
 300    SELECT @sql = 'UPDATE ##results SET ' + @update + ' FROM ##results t3'
 301    IF @verbose = 1 SELECT @sql AS [Create the SET clause of the UPDATE sql]
 302    EXEC sp_executesql @sql
 303    
 304    FETCH NEXT FROM xcursor INTO @indx, @col, @pivot
 305  
 306   END
 307  
 308   CLOSE xcursor
 309   DEALLOCATE xcursor 
 310  
 311   -- Return the results
 312   SELECT @sql = 'SELECT ' + @select + ' FROM ##results'
 313   IF @verbose = 1 SELECT @sql AS [Create the SELECT statement that will return the results]
 314   EXEC sp_executesql @sql
 315  
 316   -- Tidy up: drop the global temporary tables
 317   SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
 318   SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
 319   EXEC sp_executesql @sqlX
 320   SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
 321   EXEC sp_executesql @sqlX
 322   SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
 323   EXEC sp_executesql @sqlX
 324   SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
 325   EXEC sp_executesql @sqlX
 326   SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
 327   EXEC sp_executesql @sqlX
 328   SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
 329   EXEC sp_executesql @sqlX
 330  
 331  END

The following script creates two tables (one of test data, the other to specify a sort order) and runs the genericCrosstab procedure to generate a report.

   1  
   2  IF EXISTS (SELECT *
   3               FROM dbo.sysobjects
   4              WHERE [id] = object_id(N'[dbo].[testTable]')
   5                AND OBJECTPROPERTY(id, N'IsTable') = 1)
   6  BEGIN
   7    DROP TABLE [dbo].[testTable]
   8  END
   9  GO
  10  
  11  CREATE TABLE testTable
  12   ([who]  VARCHAR(10)  NOT NULL,
  13    [when] DATETIME     NOT NULL,
  14    [what] VARCHAR(255) NOT NULL,
  15    [qty]  INT          NOT NULL)
  16  GO
  17  
  18  DELETE FROM testTable
  19  GO
  20  
  21  INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding',  5)
  22  INSERT INTO testTable VALUES ('Jo',   '2005-APR-01', 'Coding',  1)
  23  INSERT INTO testTable VALUES ('Jo',   '2005-APR-01', 'Testing', 7)
  24  INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding',  3)
  25  INSERT INTO testTable VALUES ('Tim',  '2005-APR-01', 'Coding',  4)
  26  INSERT INTO testTable VALUES ('Fred', '2005-APR-02', 'Coding',  8)
  27  INSERT INTO testTable VALUES ('Jo',   '2005-APR-02', 'Coding',  2)
  28  INSERT INTO testTable VALUES ('Jo',   '2005-APR-02', 'Testing', 6)
  29  INSERT INTO testTable VALUES ('Tim',  '2005-APR-02', 'Coding',  4)
  30  GO
  31  
  32  SELECT * FROM testTable
  33  GO
  34  
  35  EXEC [dbo].[genericCrosstab]
  36   @sqlSelect    = 'SELECT *',
  37   @sqlFromWhere = 'FROM testTable',
  38   @pivotExpr    = 'what',
  39   @valueExpr    = 'qty',
  40   @function     = 'SUM',
  41   @groupBy      = 'who'
  42  GO
  43  
  44  -- Extending the example to proscribe column order...
  45  
  46  IF EXISTS (SELECT *
  47               FROM dbo.sysobjects
  48              WHERE [id] = object_id(N'[dbo].[testSorter]')
  49                AND OBJECTPROPERTY(id, N'IsTable') = 1)
  50  BEGIN
  51    DROP TABLE [dbo].[testSorter]
  52  END
  53  GO
  54  
  55  CREATE TABLE testSorter
  56   ([label]      VARCHAR(255) NOT NULL,
  57    [sort_order] INT          NOT NULL)
  58  GO
  59  
  60  DELETE FROM testSorter
  61  GO
  62  
  63  INSERT INTO testSorter VALUES ('Testing',  1)
  64  INSERT INTO testSorter VALUES ('Coding',   2)
  65  GO
  66  
  67  SELECT * FROM testSorter
  68  GO
  69  
  70  EXEC [dbo].[genericCrosstab]
  71   @sqlSelect    = 'SELECT *',
  72   @sqlFromWhere = 'FROM testTable',
  73   @pivotExpr    = 'what',
  74   @valueExpr    = 'qty',
  75   @function     = 'SUM',
  76   @groupBy      = 'who',
  77   @sortLookup   = 'testSorter',
  78   @verbose      = 0
  79  GO

You can change @verbose to 1 to see what is going on.

This function is not particularly smart - it expects a UK-style date string in 'dd/mm/yyyy' format, but can cope with 'dd/mm/yy' (and if mm > 12 it assumes the date is 'mm/dd/yyyy'). This work is licensed under a Creative Commons Attribution 2.5 License.

   1  
   2  CREATE FUNCTION dbo.f_convert_str_to_date(@str VARCHAR(10))
   3    RETURNS DATETIME
   4  AS
   5  BEGIN
   6  
   7    -- Declare local variables
   8    DECLARE @boundary  TINYINT
   9    DECLARE @separator CHAR(1)
  10    DECLARE @return    VARCHAR(11)
  11    DECLARE @index1    INT
  12    DECLARE @index2    INT
  13    DECLARE @day       VARCHAR(2)
  14    DECLARE @month     VARCHAR(2)
  15    DECLARE @year      VARCHAR(4)
  16    DECLARE @iDay      TINYINT
  17    DECLARE @iMonth    TINYINT
  18    DECLARE @iYear     SMALLINT
  19    DECLARE @iSwap     TINYINT
  20  
  21    -- 'Constants'
  22    SELECT @boundary = 20
  23    SELECT @separator = '/'
  24  
  25    -- Indexes of forward slash separators (2 are expected)
  26    SELECT @index1 = CHARINDEX(@separator, @str)
  27    SELECT @index2 = CHARINDEX(@separator, @str, @index1 + 1)
  28  
  29    -- Get the day, month and year
  30    SELECT @day    = LTRIM(RTRIM(LEFT(@str, @index1 - 1)))
  31    SELECT @month  = LTRIM(RTRIM(SUBSTRING(@str, (@index1 + 1), (@index2 - @index1) - 1)))
  32    SELECT @year   = LTRIM(RTRIM(RIGHT(@str, (LEN(@str) - @index2))))
  33  
  34    -- Convert the values to integer representations (will throw an error if they can't be converted)
  35    SELECT @iDay   = CAST(@day AS TINYINT)
  36    SELECT @iMonth = CAST(@month AS TINYINT)
  37    SELECT @iYear  = CAST(@year AS SMALLINT)
  38  
  39    -- Swap the day and month if they're obviously in the wrong format
  40    IF @iMonth > 12
  41    BEGIN
  42      SELECT @iSwap  = @iMonth
  43      SELECT @iMonth = @iDay
  44      SELECT @iDay   = @iSwap
  45    END
  46  
  47    -- Convert back to string representations
  48    SELECT @day    = CAST(@iDay AS VARCHAR(2))
  49    SELECT @month  = CAST(@iMonth AS VARCHAR(2))
  50    SELECT @year   = CAST(@iYear AS VARCHAR(4))
  51  
  52    -- If the day and/or month and/or year are a single digit, prefix with a zero
  53    SELECT @day    = CASE WHEN LEN(@day)   = 1 THEN '0' + @day   ELSE @day   END
  54    SELECT @month  = CASE WHEN LEN(@month) = 1 THEN '0' + @month ELSE @month END
  55    SELECT @year   = CASE WHEN LEN(@year)  = 1 THEN '0' + @year  ELSE @year  END
  56  
  57    -- If the year is only 2 digits long, prefix with '19' or '20' (depending on the boundary)
  58    IF LEN(@year) = 2 SELECT @year = CASE WHEN @iYear < @boundary THEN '20' + @year ELSE '19' + @year END
  59  
  60    -- Build the cleaned up date string, with the month number converted to a string expression
  61    SELECT @return = @day + '-'
  62                   + CASE @month
  63                       WHEN '01' THEN 'JAN'
  64                       WHEN '02' THEN 'FEB'
  65                       WHEN '03' THEN 'MAR'
  66                       WHEN '04' THEN 'APR'
  67                       WHEN '05' THEN 'MAY'
  68                       WHEN '06' THEN 'JUN'
  69                       WHEN '07' THEN 'JUL'
  70                       WHEN '08' THEN 'AUG'
  71                       WHEN '09' THEN 'SEP'
  72                       WHEN '10' THEN 'OCT'
  73                       WHEN '11' THEN 'NOV'
  74                       WHEN '12' THEN 'DEC'
  75                     END
  76                   + '-' + @year
  77  
  78    -- Done
  79    RETURN CAST(@return AS DATETIME)
  80  
  81  END

Example of usage:

   1  
   2  SELECT dbo.f_convert_str_to_date('21/8/01')

This work is licensed under a Creative Commons Attribution 2.5 License.

   1  
   2  WHILE CHARINDEX('  ', @string) > 0 SELECT @string = REPLACE(@string, '  ', ' ')

where @string is the parameter or variable.

@base can be anything from 2 to 36. This work is licensed under a Creative Commons Attribution 2.5 License.

   1  
   2  CREATE FUNCTION dbo.f_convert_to_base10
   3    (@string VARCHAR(255), @base TINYINT)
   4  RETURNS INT AS 
   5  BEGIN
   6  
   7    -- Declarations
   8    DECLARE @return INT
   9    DECLARE @len INT
  10    DECLARE @finished BIT
  11    DECLARE @pos INT
  12    DECLARE @thischar CHAR(1)
  13    DECLARE @thisasc INT
  14    DECLARE @val INT
  15  
  16    -- Initialise
  17    SELECT @base     = CASE WHEN @base < 2 OR @base IS NULL THEN 2 WHEN @base > 36 THEN 36 ELSE @base END
  18    SELECT @return   = 0
  19    SELECT @finished = 0
  20    SELECT @string   = UPPER(@string)
  21    SELECT @len      = DATALENGTH(@string)
  22  
  23    -- Failsafe
  24    IF @len = 0
  25       SELECT @finished = 1
  26  
  27    -- Loop over all characters: capitalise first character and those after spaces, replace underscores with spaces
  28    SELECT @pos = 0
  29  
  30    WHILE @finished = 0
  31    BEGIN
  32  
  33      SELECT @pos = @pos + 1
  34  
  35      IF @pos > @len
  36  
  37         -- If we've run out of characters, we're done
  38         SELECT @finished = 1
  39  
  40      ELSE
  41      BEGIN
  42  
  43         -- Get the character (from right to left)
  44         SELECT @thischar = SUBSTRING(@string, (@len - (@pos - 1)), 1)
  45  
  46         -- Get the character's ASCII value
  47         SELECT @thisasc  = ASCII(@thischar)
  48  
  49         -- Convert to a numerical value
  50         SELECT @val = CASE
  51                         WHEN @thisasc BETWEEN 48 AND 57 -- '0' AND '9'
  52                           THEN @thisasc - 48
  53                         WHEN @thisasc BETWEEN 65 AND 90 -- 'A' (= decimal 10) AND 'Z'
  54                           THEN @thisasc - 65 + 10
  5