Remove Characters in String T-SQL with No Loop Part 2

Okay, expanding on what I did yesterday.

I Built a table, called ExclusionList with one field, ExcludedText



Then created a function:

CREATE FUNCTION [dbo].[udf_clean_string]
	@string VARCHAR(100)

    @string = REPLACE(@string,ExcludedText,'')
FROM ExclusionList

RETURN @string

You can then use like this:

FROM TableName
Posted in Programming | Tagged , , , , | Leave a comment

Remove Characters in String T-SQL with No Loop

Ok, I’ve seen many ways to do this using loops, but nothing in plain T-SQL

Let’s say we have a string that may contain illegal characters, in my case it was project identifiers that needed to be used as file names for exporting. However, many had / or * in the names.

One thing I hate in T-SQL is loops, so having only found looping ways to solve this I started from scratch.

He’s my solution:

Build a table of exclusions, i.e. characters or strings that you want to remove from your string (in my example below this is a temp table, but I would make it a permanent table in the end solution).

Then use REPLACE joined to this table to clean the string e.g.

/*Create the temp table for the exclusion list table*/
CREATE TABLE #ExclusionList
	ExcludedChar VARCHAR(50)

/*Add the stuff we want to exclude*/
INSERT INTO #ExclusionList SELECT '\'
INSERT INTO #ExclusionList SELECT '|'
INSERT INTO #ExclusionList SELECT '?'
INSERT INTO #ExclusionList SELECT '*'
INSERT INTO #ExclusionList SELECT ':'
INSERT INTO #ExclusionList SELECT '<'
INSERT INTO #ExclusionList SELECT '>'
INSERT INTO #ExclusionList SELECT '"'
INSERT INTO #ExclusionList SELECT 'egg'

/*Get the horrible string that we want to clean*/
DECLARE @string VARCHAR(50) = 'th""<i>???s:: :|||"<egg>wo|r|k|**s*< ">we*l::l::'

/*Do the SELECT*/
	@string = REPLACE(@string,ExcludedChar,'')
FROM #ExclusionList

/*Show the string post processing*/
SELECT @string

/*Drop the temp table*/
DROP TABLE #ExclusionList

If you run this you’ll see that all the excluded text has been stripped and you’re left with “this works well”.

I’m all ready thinking of ways this could be expanded, e.g. Exclusion List type added to the exclusion list, you could then maintain different lists for different things, e.g. File Names, Email Addresses, etc and just add a WHERE clause to the REPLACE select statement.

Any comments or ideas please let me know!

Posted in Programming | Tagged , , , , , , | Leave a comment


You can buy my programming and computer related gifts from my shop!

Posted in Uncategorized | Leave a comment

Dynamic CSS files in Master Page using C#

I have a reporting portal that serves up SSRS (SQL Server Reporting Services) reports to users. The users are from different organisations, indicated by a user group. I needed to have the front end personalised for each different organisation. The approach below loads a different CSS file depending on the user group of the logged in user. I’m using a master page solution, so the CSS is loaded in the master page file. The CSS files for each organisation are stored in a CSS folder.

If we look at a standard link to a style sheet that we would place in the head of our master page

<head runat="server">
    <link href="CSS/StyleSheet.css" type="text/css" rel="stylesheet" />

We are going to change this to what we see below

<head runat="server">
    <link id="CurrentStyleSheet" type="text/css" rel="stylesheet" runat="server"/>

You need to note three things in the above code. We need to ensure that runat=”server” is added, that there is no “href” to point to our CSS file as we be adding this in code, and we have given our link an id.

Firstly lets load the CSS file in the Page_Load event of the master page. We reference the link using its id and add the “href” attribute and value to the link (the !Page.IsPostBack stops this from being done repeatedly on every page postback).

        protected void Page_Load(object sender, EventArgs e)
            if (!Page.IsPostBack)
                CurrentStyleSheet.Attributes.Add("href", "CSS/StyleSheet.css");

This is all good, however we are still just loading one CSS file regardless. In my database I have a field that holds the CSS file name for each user group and a function getCssFile() that returns the CSS file name for the logged in user. (I’m not going to cover how that function reads the value from the database here).

So the code now becomes

        protected void Page_Load(object sender, EventArgs e)
            if (!Page.IsPostBack)
                CurrentStyleSheet.Attributes.Add("href", string.Format("CSS/{0}", getCssFile()));

This goes and gets the name of the style sheet from the database for the logged in user and inserts this into the “href” attribute.

Posted in Programming | Tagged , , , , , , | Leave a comment

Hotpoint Mistral Fridge Freezer Temperature Problems.

If your Hotpoint Fridge Freezer is reading a high temperature for the Fridge check this before you spend out any money.

After having my kitchen refitted over the last few weeks my machine was reading 24 deg C for the fridge compartment. I had moved the machine a few times and figured that something had got damaged / dislodged. What was confusing me was, how is the temperature getting that high? Even if I turned it off it wouldn’t get anywhere near that warm.

Now I don’t know much about refrigeration units, however, I guess like most electrical devices they produce heat as a by product, but enough to heat the fridge to 24 deg while the freezer was still at -20 (the freezer temp was fluctuating but only by 1 – 2 deg)?.

But one thing that can produce heat in your fridge, is the lamp…

It turns out that moving the machine about can cause the door to drop. This can mean that the door is not high enough to trigger the switch that turns off the lamp. I was unable to get the door level again, so, as the door was still sealing, I fixed with a small piece of double-sided numberplate tape and a small square of flat plastic stuck to the top of the door where the switch contacts.

Temp all back to normal within about an hour.

You can quickly test if this is your problem by removing the lamp from the inside of the fridge compartment for a short time and see if the temp drops.

I have previously replaced the thermistors in my machine when I had issues with the Freezer temperature, there are lots of examples on the internet if you need to do that.

Posted in Uncategorized | Tagged , , , , , , , , , | Leave a comment

Another new blog

My idea of funny

Posted in Uncategorized | Leave a comment

New Photo Blog

To keep everything nice and tidy I’ve started a new blog for my photography ‘stuff’.

It can be found here Photos!

Not much to see at the moment but will build over the next few weeks.

Posted in Photography | Tagged , , , | Leave a comment