T-SQL Conversion failed when converting the varchar to data type int
A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on.
How?
CharIndex Reverse - find occurrence starting from end of string in TSQL
This is a quick note on finding the last occurrence of a string in a longer string. This has to be in Transact SQL for a SQL Server instance only and not filtered by other code.
Why?
I have a string such as the following (column positions added for demo purposes):
- String1.String2.String3.String4
- 1 5 10 15 20 25 30 -> length = 31
For argument's sake, I'm assigning this string to the variable "haystack".
How?
Perhaps we should determine the position of the last needle first (reverse the haystack string and find needle):
DataScramble - Randomizing data rows
Along with my DataJumble function and DataTumble procedure which also help scramble database tables sent to suppliers/developers, this is a function which simply finds random characters and inserts these.
I would recommend using the DataTumble script over this one as this leaves data very difficult to work with:
- Before:
- StudentID StudentName DateOfBirth
- ----------- -------------------- -------------
- 1 John Smith 1990-03-21
- 2 Fred Bloggs 1988-11-02
- 3 Another User 1985-07-11
- 4 Yet Another User 1977-06-25
- After:
- StudentID StudentName DateOfBirth
- ----------- -------------------- -------------
- 1 PDUHjRWJcb 1926-01-02
- 2 WRmNqQKxvuV 1969-03-14
- 3 nBCkAVDrvdhe 1968-05-05
- 4 RJDsFMaeNcLrcMWw 1964-08-08
How?
DataTumble - Randomize Data Rows
This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and matching data values:
Before:
- ID Name DateOfBirth
- --------- ------------ -----------
- 1 John Smith 1988-06-24
- 2 Fred Bloggs 1972-11-17
- 3 Another User 1964-02-18
- ID Name DateOfBirth
- --------- ------------ -----------
- 1 Fred Bloggs 1964-02-18
- 2 Another User 1988-06-24
- 3 John Smith 1972-11-17
How?
Search a database for a value and count matching rows
We have a datawarehouse and we want to be able to count all the records in any table of the database that match on a particular warehouse load. This is a column value where the column is called "WarehouseLoadKey" and the value we want to search on is "3" (the 3rd incremental load).
How?
The below stored procedure can be reduced to just a script as long as you declare and set the parameters after the BEGIN and extract the script from BEGIN to END (excluding the words BEGIN and END - avoids the need to create a stored procedure and saving it on a database):