SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Split function, at least kind of, needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 04/10/2013 :  09:29:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/10/2013 :  09:48:45  Show Profile  Reply with Quote
-- 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))
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/10/2013 :  09:52:45  Show Profile  Reply with Quote
--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+'/%'
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/11/2013 :  02:51:28  Show Profile  Reply with Quote
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
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 04/17/2013 :  08:15:22  Show Profile  Reply with Quote
Thanks, I went with that UDF, works nicely!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/17/2013 :  08:17:58  Show Profile  Reply with Quote
quote:
Originally posted by KilpAr

Thanks, I went with that UDF, works nicely!


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000