SQL Function to transform rows into delimited text

CREATE FUNCTION CitiesAsDelimitedString
(
@Delimiter AS VARCHAR(5)
)
RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @str AS VARCHAR(8000)
SET @str = ''

SELECT @str=@str+CASE @str WHEN '' THEN CityName ELSE @Delimiter + CityName END
FROM Cities

END
---------------------------------------------------------

Usage:
select dbo.CitiesAsDelimitedString(', ')

SQL Function to parse a delimited string

CREATE FUNCTION ParseDelimitedString
(
@str AS VARCHAR (8000),
@delimiter AS VARCHAR (5)
)
RETURNS @RETTABLE TABLE(string VARCHAR(50))

AS

BEGIN
WHILE LEN(@STR) > 0
BEGIN
IF CHARINDEX(@DELIMITER, @STR) = 0
BEGIN
INSERT INTO @RETTABLE VALUES (LTRIM(RTRIM(@STR)))
SET @STR = ''
END
ELSE
BEGIN
INSERT INTO @RETTABLE
VALUES (SUBSTRING(@STR, 1, CHARINDEX(@DELIMITER, @STR)-1))
SET @STR = RIGHT(@STR, LEN(@STR) - CHARINDEX(@DELIMITER, @STR) - 1)
END
END

RETURN
END
--------------------------------------------------------------------------------------------------------------------------
Usage:
select * from dbo.ParseDelimitedString('cat, dog, frog, pig', ', ')

VS annoyance

I was annoyed to find that the file under edit was not selected in the Solution explorer. After a quick Google search, I found the solution:

Tools -> Options -> Projects and Solutions [tree view] -> general [tree view
node] -> "Track Active Item in Solution Explorer" [check box]

Configure Oracle TNS names file

C:\Oracle\product\10.1.0\Client_1\network\ADMIN\tnsnames.ora

# tnsnames.ora Network Configuration File: C:\Oracle\product\10.1.0\Client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORADB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux1.schoolnet.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORADB.schoolnet.com)
)
)

Interesting article on Controls.BeginInvoke

http://www.codeproject.com/csharp/begininvoke.asp

Did you know (.NET)

Did you know that System.Delegate is not a delegate type and that System.ValueType is not a value type?

These are both marker classes, and you have to be derived from them to qualify as a delegate or value type respectively.

Must have utilities

These utilities are very useful:

• JRuler


• Lutz Roeder's .NET Reflector


• nAnt


• RegEx Buddy


• Trillian


• Visual XPath


• WebServices Studio

Oracle weirdness redux

Okay, this is basically just a difference between SQL Server and Oracle. It isn't really weird but you should be aware of it:

Cast ('xxx' AS datetime) doesn't work because datatime is not an Oracle type. The correct way to do this in Oracle is by using the TO_DATE function like this:

TODATE('xxx')

In my app, I use a data access layer to compensate for Oracle's differences. The first string can easily be converted to the second by using the Regex.Replace method:

sql = Regex.Replace(sql,@"cast\((.+) as datetime\)",
"TO_DATE($1)",
RegexOptions.IgnoreCase);

SQL Function to convert Guids to Oracle format

--Function to convert SQL Server (Windows) guid to Oracle guids
create function dbo.ConvertGuids(@id as uniqueidentifier)
returns varchar(32)
as
begin
declare @str varchar(36),@res varchar(32)
set @str=replace(convert(varchar(36),@id),'-','')
set @res=substring(@str,7,2)+substring(@str,5,2)+substring(@str,3,2)+substring(@str,1,2)+
substring(@str,11,2)+substring(@str,9,2)+substring(@str,15,2)+substring(@str,13,2)+
substring(@str,17,100)
return (@res)
end

====================================
To generate a new GUID in SQL*Plus:
select SYS_GUID() from dual;

VS.NET 2003, VSS 6.0d and the "Refreshing the project failed. Unable to retrieve folder information from the server" error

To fix this annoying error, delete this folder:
C:\Documents and Settings\kharris.DEVSCHOOLNET\VSWebCache\