Splitting a field and puuling out related stuff? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Splitting a field and puuling out related stuff?

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 06:29:12

Hi

I have a field (field1) in a table which contains a list of number seperated by commas.:-
12345, 23456, 3434488, 77656554578, 12322

I have another table which has a field (field2) which contains an instance of one of the (record) numbers in field1 above. e.g it may contain the number 12345 from the list above.

I need to pull the numbers out in the order they appear in the field and then extract any records related to each number found in another table.
Can anyone help please?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-27 : 06:39:14

So many posts about this on this site. Have you searched for answers yet.

Here's one way.


IF EXISTS (
	SELECT * from dbo.sysobjects
	WHERE 
		id = object_id(N'[dbo].[fn_Split]')
		AND xtype IN (N'FN', N'IF', N'TF')
	)
	DROP FUNCTION [dbo].[fn_Split]
GO


CREATE FUNCTION fn_Split (@text VARCHAR(8000), @delimiter VARCHAR(20) = ' ')

	RETURNS @Strings TABLE (
		position INT IDENTITY PRIMARY KEY
		, value VARCHAR(8000)
		)

AS BEGIN
	DECLARE @index int
	SET @index = -1

	WHILE (LEN(@text) > 0) BEGIN
		-- Find the first delimiter
		SET @index = CHARINDEX(@delimiter , @text)

		-- No delimiter left?
		-- Insert the remaining @text and break the loop
		IF (@index = 0) AND (LEN(@text) > 0) BEGIN  
			INSERT INTO @Strings VALUES (@text)
			BREAK 
		END 

		-- Found a delimiter
		-- Insert left of the delimiter and truncate the @text
		IF (@index > 1) BEGIN
			INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
			SET @text = RIGHT(@text, (LEN(@text) - @index))
		END
		-- Delimiter is 1st position = no @text to insert
		ELSE SET @text = RIGHT(@text, (LEN(@text) - @index))
	END
	RETURN
END
GO

SELECT * FROM dbo.fn_split('123,12,12,55546,2341,231,23123', ',')

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 06:39:49

may be this

SELECT *
FROM table1 t1
INNER JOIN table2 t2
ON ','+t1.field1+',' LIKE '%,' + CAST(t2.field2 AS varchar(10))+ ',%'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 06:45:36

or this:-

SELECT t2.*
FROM table2 t2
INNER JOIN (SELECT t.field1,b.ID,b.Val
            FROM table1 t1
            CROSS APPLY dbo.ParseValues(t1.field1) b
           )t1
ON t1.Val=t2.field2
ORDER BY t1.ID

ParseValues can be found in below link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 06:53:02

Sorry and thanks all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 07:04:24

quote:
Originally posted by Bill_C

Sorry and thanks all

welcome

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 07:37:15

Sorry , another quick one

Would that function work if I wanted to pull out the whole row for every instance of a sequence found in that field?

e.g
filed1 = 12345, 23456, 3344556

Then in the same table:-

filed2 = 'Mr'
field3 = 'Johnson'
field4 = 'hello'

And say i wanted the output to read:-
field1 - field2 - field3 - field4
12345 - Mr - Johnson - hello
23456 - Mr - Johnson - hello
3344556 - Mr - Johnson - hello

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 07:42:08

on what basis you'll link field2,3,4... to field1's value?is there another linking field?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 07:53:44

Your function does split them properley,
SELECT t.*,b.Val FROM dbo.tablename t
CROSS APPLY ParseValues(t.fieldname)b

but how do I get a counter for each instance of the number sequences found?

e.g.

counter - field1 - field2 - field3 - field4
1 - 12345 - Mr - Johnson - hello
2 - 23456 - Mr - Johnson - hello
3 - 3344556 - Mr - Johnson - hello

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 07:55:28

at the moment it comes out like this:

EncounterRecNo - field1 - field2 - field3 - field4
1 - 12345 - Mr - Johnson - hello
1 - 23456 - Mr - Johnson - hello
1 - 3344556 - Mr - Johnson - hello

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 07:58:46

on what relation do you link the other fields to this?

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 08:08:06

I now have a table with all fields in one row, and I pull out the row once for each sequence of numbers found in field1.

So i now have a table with data as follows:-

field1 = 12345, 23456, 3344556
filed2 = 'Mr'
field3 = 'Johnson'
field4 = 'hello'

and I need to split field1 up and display the whole row (field1, 2,3 & 4) once for each time a number exists in field1

EncounterRecNo - field1 - field2 - field3 - field4
1 - 12345 - Mr - Johnson - hello
2 - 23456 - Mr - Johnson - hello
3 - 3344556 - Mr - Johnson - hello

I can see in the function that there is an identity for ID, and i need output like above?

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 08:10:45

just do this

SELECT b.ID,b.Val,t.field2,t.field3,t.field4
FROM table t
CROSS APPLY dbo.ParseValues(t.field1) b

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2008-10-27 : 08:12:01

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 08:18:17

[code]declare @Test table
(
field1 varchar(200),
field2 varchar(20),
field3 varchar(20),
field4 varchar(20)

)

insert into @test
select '12345, 23456, 3344556','Mr','Johnson','hello'

select id,Val,field2,field3,field4
from @test t
cross apply dbo.parsevalues(t.field1)b

output
------------------------------
id Val field2 field3 field4
----------- ---------- -------------------- -------------------- --------------------
1 12345 Mr Johnson hello
2 23456 Mr Johnson hello
3 3344556 Mr Johnson hello[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 08:18:38

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources