Getting file name from full file path in SQL Server – UDF

Today at office I got a task which had a table with image URLs in one of the column. The image URLs were a network path. I had to write a script to identify the image name (file name) and then update it with a web URL for the same image. I needed a function which would give me file name when given a file path. As you must know, such type of things is very easy in C#. But in SQL Server we do not have any in built function to do this. So I wrote a function of my own. Below is what I wrote & also showing how to use it. The script is self explanatory I believe J

CREATE FUNCTION [dbo].[udf_GetFileName]
	@FilePath VARCHAR(MAX),
	@Separator CHAR(1) = '\'
			LEN(@FilePath) - CHARINDEX(@Separator, REVERSE(@FilePath),1) + 2, -- From where to start
			LEN(@FilePath) - CHARINDEX(@Separator, REVERSE(@FilePath),1) -- how many characters to take

--How to use

DECLARE @networkpath VARCHAR(50) = '\\somenetworkpath\somefolder\networkfile.txt'
DECLARE @localpath VARCHAR(50) = 'D:\somefolder\localfile.txt'
DECLARE @webpath VARCHAR(50) = ''
	dbo.udf_GetFileName(@networkpath,DEFAULT) as [NetworkPath],
	dbo.udf_GetFileName(@localpath,DEFAULT) as [LocalPath],
	dbo.udf_GetFileName(@webpath,'/') as [WebPath]

The out put is as shown below


Hope it helps!

Opening Visual studio in older version after converting to a higher version

I have many times faced a problem of converting a Visual studio project to a higher version (say 2010) and then someone coming to me and saying that it needs to be in the older version only.

But problem with Visual studio is, once you convert from a lower to higher version, that project will not open back in the lower version. You may a error saying “The project was created using higher version and cannot be open”.

Well, i have found out a quick way to overcome this problem.

Every project file (.csproj OR .vbproj) is nothing but an XML File. When you convert a project, what it does is update few values in the XML file. What you need to do is, undo the changes done by the converter in the project XML file and you are good to go. To do that, Right click on the project file & edit it with your favorite editor (like notepad++).

Once you open it, search for the line shown in below screen shot.

Notice the “v9.0” this what tells Visual studio which version to use to open the project. So if you have converted a Project from VS 2008 to VS 2010, then this particular value would be “V10.0“. To open it back with VS 2008, just change it to “v9.0″ and then you should be able to open it with VS 2008.

Hope this quick & handy tip helps you :)

Technorati : , , , : , , ,

JQuery Rating plug-in – Not working in IE 8 – FIXED

Recently I was working on a web app where it was required to keep a Rating control (with 5 stars). Instead of re-inventing the wheel, I thought of using one of the existing plug-in. What can be better than a JQuery rating plug-in. I quickly went to the JQuery plug-in site here and studied the demo and implemented the script. It worked very smoothly!

But my joy did not last long L. The moment I opened my web page in IE 8, the rating control was not behaving properly, stars which were pre-selected (like loading the rating value from DB) used to come blank. The same was working in IE 7, but not in IE 8.

So I did some dissection on the JQuery Rating plug-in and modified it to work with IE 8. Below are the changes which I did,

Basically the problem was with this line,

if (0 !== $(“#” + id + ” option[selected]”).size())

IE 8 does not understand this tag option[selected]for that reason, always the “.size()” returns “0”. One option is to replace the above tag with option:selectedbut then our IE 8 will by default select the first element in the drop down (select) box and so even if no rating is given, the first star will be selected. This is IE’s behaviour. To overcome this problem, I modified the rating.js file,

var selectOptions = document.getElementById(id).getElementsByTagName("option");
    var selectOptionCount = 0;
    for (var i = 0; i < selectOptions.length; i++) {
        var sValue = selectOptions[i].className;
        if (sValue == "selected") {
            selectOptions[i].selected = true;

For above code to work, the only change you need to do is, instead of putting selected=”selected” for the selected item, put class = “selected” and the rating plug-in will automatically take care. This has been tested in IE 7, IE 8, Firefox 3.6.17, 4.0 & Chrome.

You can download the modified JQuery Rating Plug-in here

Hope it helps!

Using the IN Clause in Stored procedure – SQL Server

Yesterday, a junior developer came to me and told me that he converted a Stored Procedure to inline query because he was not able fetch data using the ‘IN’ clause from stored procedure. Let me demonstrate the problem using an example,

Let us first create a temporary table and create a stored procedure

The requirement was something like this,

But when used with stored procedure, it was not returning any records (see below)


Creating a function,

Altering the same stored procedure which we created earlier to use the function

The output,

–Run the same procedure again

usp_select_details ‘A1,A3,A5′

Clean up code,

WebService Studio – To the rescue

Recently at work I was working with couple of web services. After the services were developed we needed a way to test all those pages. What we thought that we can develop one generic page and that will help us to test all the services we developed. But that involved a lot of effort and unfortunately we were out of time. So what we did is we developed a test page for each of the services. But we ended with issues in the test page itself. Yesterday I thought of developing one single page for testing purpose and in that process I came across a tool called “WebService studio”. This tool was developed by Microsoft but now it is supported by CodePlex. This is one great tool, we just need to give the web service URL and it will show up all the web methods and their input parameters. One good thing is that, it also supports Custom objects as input. Still I am in process of finding its limitation but as of now this is savior for me. J

Updating a column value and making them all of one length

Today in office we had to run a job on a set of records given by client. The data was in form of an excel sheet which had some employee ids. We imported the data from excel to a table. We quickly found out that the employee ids in the excel sheet are not of the same length. For our job to run correctly it was required that all the values in the employee_id column be of length 8. As we were out of time, I quickly wrote 3 update statements to modify the employee id column. The update statements were as below,

UPDATE tbl_employee SET emp_id=‘000’+emp_id WHERE LEN(emp_id)=5
UPDATE tbl_employee SET emp_id=’00’+emp_id WHERE LEN(emp_id)=6
UPDATE tbl_employee SET emp_id=‘0’+emp_id WHERE LEN(emp_id)=7
We had three types of employee ids with length 5,6 & 7. It worked perfectly fine. But later I thought that this is not the right way to do. So I quickly did some findings and came up with a new single query which will do the job. Let me explain with an example,
CREATE TABLE tbl_employee(emp_id varchar(8),emp_fname varchar(25))
INSERT INTO tbl_employee(emp_id,emp_fname)
/* See values in tabels */
SELECT * FROM tbl_employee

/* We want to update all the emp_id so that all are of lenght 8 */
UPDATE tbl_employee SET emp_id=RIGHT(‘00000000’+emp_id,8)
/* See the new values in tables */
SELECT * FROM tbl_employee