Split function, at least kind of, needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split function, at least kind of, needed

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-04-10 : 09:29:09

I have a problem where I have defined project numbers like a/b. Now in the database I have where invoices are listed by either a or b.

I need to receive the first invoice date, but I can't figure how to deal with the project numbers.

I'm trying with:


	DECLARE @ProjectID varchar(64);
	SET @ProjectID = 'a/b';
	
SELECT MIN(Date_) AS startdate
	FROM mydatabase
	WHERE projectnumber IN(
	SELECT '''' + REPLACE ( @ProjectID, '/' , ''',''' ) + ''''
	)

It returns NULL.

If I replace the in clause with
'a','b'
it works immediately.

I believe I need some split type function, but what's the smartest way to go from here?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-10 : 09:48:45

[code]-- CustomSplit UDF
CREATE FUNCTION [dbo].[CustomSplit] (@sep char(1), @s varchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(n, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT n + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT n,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS Val
FROM Pieces
)
GO

DECLARE @ProjectID varchar(64);
SET @ProjectID = 'AD_VP/SA_REP';

SELECT MIN(HIRE_DATE) AS startdate
FROM EMPLOYEES
WHERE JOB_ID IN( SELECT Val FROM CustomSplit('/', @ProjectID))
[/code]

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-10 : 09:52:45

--Alternate is
Note: This is feasible for small set of values as well as delimiter should be / without any spaces

DECLARE @ProjectID varchar(64);
SET @ProjectID = 'AD_VP/SA_REP';

SELECT MIN((Date_) AS startdate
	FROM mydatabase
	WHERE '/'+@ProjectID+'/' LIKE '%/'+projectnumber+'/%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-11 : 02:51:28

here's another method

http://visakhm.blogspot.in/2013/01/delimited-string-split-xml-parsing.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2013-04-17 : 08:15:22

Thanks, I went with that UDF, works nicely!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-17 : 08:17:58