Transact-SQL - Joel Lipman .Com

T-SQL Conversion failed when converting the varchar to data type int

: Joel Lipman; Transact-SQL; 14 May 2013; Hits: 31836

What?
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?

: Joel Lipman; Transact-SQL; 28 January 2013; Hits: 40459

What?
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):

copy raw

String1.String2.String3.String4
1   5   10   15   20   25   30 -> length = 31

String1.String2.String3.String4
1 5 10 15 20 25 30 -> length = 31

I'd like to end up with just the last part of this, ie "String4". So I need to delimit based on the dot/period (.) and use substring in a sort of reversed form.

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):

Category: Transact-SQL :: Article: 478

: Joel Lipman; Transact-SQL; 07 December 2012; Hits: 7462

What?
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:

copy raw

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

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?

Category: Transact-SQL :: Article: 460

: Joel Lipman; Transact-SQL; 07 December 2012; Hits: 5448

What?
This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and matching data values:

Before:

copy raw

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 John Smith 1988-06-24
2 Fred Bloggs 1972-11-17
3 Another User 1964-02-18

After:

copy raw

ID        Name         DateOfBirth
--------- ------------ -----------
1         Fred Bloggs  1964-02-18
2         Another User 1988-06-24
3         John Smith   1972-11-17

ID Name DateOfBirth
--------- ------------ -----------
1 Fred Bloggs 1964-02-18
2 Another User 1988-06-24
3 John Smith 1972-11-17

Looks pretty good, doesn't it? The advantages of this is that you can send this data to your developers and the data types will be correct and maybe they'll resolve issues faster than if they were given scrambled data (see my articles on DataJumble and DataScramble).

How?

Category: Transact-SQL :: Article: 459

: Joel Lipman; Transact-SQL; 26 October 2012; Hits: 7787

What?
Yes you could just run a SELECT DISTINCT query along with collation to make it case-sensitive and/or accent-sensitive but using this stored procedure means I only specify the table and then the columns.

How?

: Joel Lipman; Transact-SQL; 10 October 2012; Hits: 8525

What?
This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on case-sensitivity.

Why?
We have a database with data in it. For a particular column we have setup default values, let's use the example "Data Not Yet Available". Unfortunately the end-user reported these default values sometimes list twice, especially when the case is different, eg. "Data not yet available". The final system (qlikview) was case-sensitive despite our server collation being case-insensitive.

Furthermore, we now have the task of finding all the variations of the default values which we found we could do with the built-in SOUNDEX function.

How?

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 9699

Why?
Hmm... I was writing a stored procedure that will scramble data given a table as a parameter. Because I only want to update a temporary table and not the original (source) table, I needed the following stored procedure (or part of).

What?
This will copy a given table into a temporary table all the while maintaining the structure and data.

Thinking inside of the box
I think everyone suggests the following (or at least the idea of):

copy raw

SELECT *
INTO #MyTempTable
FROM @GivenTable

SELECT *
INTO #MyTempTable
FROM @GivenTable

If @GivenTable is a parameter then the above will simply return an error. Also, if it was this easy, I wouldn't need to post this note on my website.

Category: Transact-SQL :: Article: 424

: Joel Lipman; Transact-SQL; 03 August 2012; Hits: 14618

What?
Some people have been suggesting you can use "CREATE OR REPLACE ... VIEW ... FUNCTION" but my SQL Server 2008 Management Studio doesn't like this and refuses to understand what I'm trying to do.

Why?
As this data seemed to be across various websites, I wanted a page which has all of them in one place. So here you go, I hope it's of some use. If it's wrong then just post a comment at the bottom of this page.

: Joel Lipman; Transact-SQL; 01 August 2012; Hits: 11894

This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which merely uses the same characters but switches their order randomly, so I've renamed it DataJumble as opposed to my article on Data-Scrambling.

Why?
We want to scramble sensitive data that we send to suppliers for support or analysis. With inspiration from: "Obfuscating your SQL Server Data" by John Magnabosco but tweaked for our purposes. NOTE that the following has only ever been run on development environments and I would not recommend running this on a production system as I have not tested the performance and database load.

copy raw

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           mStnoh iJh           1926-01-02
           2           lgreg BFdos          1969-03-14
           3           onrUest ehAr         1968-05-05
           4           otentre AhYe Usr     1964-08-08

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 mStnoh iJh 1926-01-02
2 lgreg BFdos 1969-03-14
3 onrUest ehAr 1968-05-05
4 otentre AhYe Usr 1964-08-08

Category: Transact-SQL :: Article: 422

: Joel Lipman; Transact-SQL; 11 October 2011; Hits: 10780

I have googled, binged and asked but to no avail. Some self-proclaimed IT experts in forums said "why would you want to do that?". Unhelpful Bellends. It's a bit like asking me "Why can't I turn on my computer" and me replying "why would you want to do that?".

Anyway, I was looking for something like the record separator in Oracle SQL*Plus where a row of data (blank or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following:

copy raw

SELECT
	DATENAME(dw, StartDate) AS 'Day'
FROM 
	Timetable
ORDER BY
	StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC

-- Yields
/*
Day              
---------------- 
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

SELECT
DATENAME(dw, StartDate) AS 'Day'
FROM
Timetable
ORDER BY
StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC
-- Yields
/*
Day
----------------
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/

Category: Transact-SQL :: Article: 394

: Joel Lipman; Transact-SQL; 23 March 2011; Hits: 7750

My setup

Windows XP SP3
MS SQL Server Management Studio 2008
SQL Server 2008 R2
Regional Setting: London BST (British Summer Time)

: Joel Lipman; Transact-SQL; 14 January 2011; Hits: 14098

So I find myself searching the web for a way of producing an outlook-style calendar within Business Intelligence Development Studio (BIDS). Weekdays along the top and then dates inside. Additionally we would like this linked to events in a database.

I'm calling this project "Reinventing the Wheel" because the request was a report that was similar to an MS Outlook calendar which will display any room bookings for that month. This is "Reinventing" because we used to use MS Outlook to store these kind of things until the company implemented a software to deal with room bookings and said we shouldn't use Outlook for this...