Simple function to convert a string to proper case
CREATE FUNCTION PROPERCASE
(
--The string to be converted to proper case
@input varchar(8000)
)
--This function returns the proper case string of varchar type
RETURNS varchar(8000)
AS
BEGIN
IF @input IS NULL
BEGIN
--Just return NULL if input string is NULL
RETURN NULL
END
<a href="http://www.fruitbatscode.com/uncategorized/proper-case-sql-function#more-293" class="more-link">Read more...</a>
I spent all weekend trying to convert a Sql Server db to a MySql v5 db and found it quite a frustrating experience! When you’re sytnax is incorrect MySql helpfully says ‘You have an error in your sql syntax near:’ and error 1064. No help with which command causes the error and the position it picks for the start of the problem is usually wrong. God we’ve been spoilt by Sql Server.
Read more…
Was explaining to a friend how to cope with duplicates today so thought I’d just stick a quick note up for him to refer too. Finding duplicates in SQL is relatively easy when dealing with one field, we just need to count the number of occurances of the field: Read more…
Just a little udf that gets midnight for a datetime.
CREATE FUNCTION dbo.Midnight (@Date datetime)
RETURNS Datetime AS
BEGIN
return CAST(FLOOR(CAST(@Date AS FLOAT)) AS DATETIME)
END
Just a couple of udf to handle paths in sql server, not very solid but if your paths are valid they work well.
CREATE FUNCTION dbo.GetFileName (@Path varchar(512))
RETURNS varchar(255) AS
BEGIN
Return RIGHT(@Path, LEN(@Path) - dbo.LAST_INDEX(@Path, '\'))
END
Read more…
Sometimes when you restore a SQL Server backup, the permissions are not carried across! Its a real pain to tick all those boxes so here is the dirty way I use to create a script to assign them.
The code below pulls out the tables and views from the database structure into a single column. Read more…