split column with part of text string

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 2000 Forums

Transact-SQL (2000)

split column with part of text string

Author

Topic

syreocna
Starting Member

2 Posts

Posted - 2004-08-30 : 11:36:05

Hi all,

I have the following nvarchar(255) column data:
Column_xx
name: johnson email: firstname: philip need: doc 12
name: johnson email: pjohnson@yahoo.com firstname: philip need: doc 13

and I would want to select the data into multiple column taking after [name:] the data until [email:]
Name |Email |FirstName |Need
johnson |<<null>> |philip |doc 12
johnson |pjohnson@yahoo.com |philip |doc 13

Please could you help me?
Thanks
Please email at syreocna@hotmail.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-30 : 13:11:59

I'm thinking udf...needs some tweaking, and I don't understand the error, based on the test code.....


USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(255))
GO

SET NOCOUNT ON
INSERT INTO myTable99(Col1)
SELECT 'name: johnson email: firstname: philip need: doc 12' UNION ALL
SELECT 'name: johnson email: pjohnson@yahoo.com firstname: philip need: doc 13'
GO

CREATE FUNCTION udf_GetString(@Col1 varchar(255), @tag varchar(255))
RETURNS varchar(255)
AS
  BEGIN
DECLARE @str varchar(255)
	SELECT	  @str = SUBSTRING(@Col1
		, CHARINDEX(@tag,@Col1)+1+LEN(@tag)
		, CHARINDEX(' ',Col1,CHARINDEX(@tag,@Col1)+1+LEN(@tag))-(CHARINDEX(@tag,@Col1)+1+LEN(@tag)))
	  FROM myTable99
	RETURN @str
  END
GO

SELECT 	  dbo.udf_GetString(Col1,'firstname:')
	, dbo.udf_GetString(Col1,'email:')
	, dbo.udf_GetString(Col1,'need:')
	, Col1
FROM myTable99
GO

DECLARE @tag varchar(255)
SELECT @tag = 'firstname:'
	SELECT	  SUBSTRING(Col1
		, CHARINDEX(@tag,Col1)+1+LEN(@tag)
		, CHARINDEX(' ',Col1,CHARINDEX(@tag,Col1)+1+LEN(@tag))-(CHARINDEX(@tag,Col1)+1+LEN(@tag)))
	  FROM myTable99
SELECT @tag = 'email:'
	SELECT	  SUBSTRING(Col1
		, CHARINDEX(@tag,Col1)+1+LEN(@tag)
		, CHARINDEX(' ',Col1,CHARINDEX(@tag,Col1)+1+LEN(@tag))-(CHARINDEX(@tag,Col1)+1+LEN(@tag)))
	  FROM myTable99
SELECT @tag = 'need:'
	SELECT	  SUBSTRING(Col1
		, CHARINDEX(@tag,Col1)+1+LEN(@tag)
		, CHARINDEX(' ',Col1,CHARINDEX(@tag,Col1)+1+LEN(@tag))-(CHARINDEX(@tag,Col1)+1+LEN(@tag)))
	  FROM myTable99
GO


SET NOCOUNT OFF
DROP FUNCTION udf_GetString
DROP TABLE myTable99
GO

Brett

8-)

syreocna
Starting Member

2 Posts

Posted - 2004-08-31 : 05:53:16

Thanks
I have tested it and have the following error message:
Server: Msg 536, Level 16, State 3, Procedure udf_GetString, Line 6
Paramètre de longueur incorrect passé à la fonction de sous-chaîne.
which defines the "incorrect parameter lenght passed through the function"

Any extra thoughts?

Thanks
Syreocna

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 06:13:36

that means you passed a field longer that the one specified as UDF parameter. longer than 255 chars.
put a longer varchar @Col1 varchar(500) or @tag varchar(500))

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources