Handling array fields - SQL Server Forums

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

Handling array fields

Author

Topic

sbhegel
Starting Member

19 Posts

Posted - 2005-11-30 : 10:29:41

Hi,

I have a linked db in my sql database and one of the fields I am trying to query is an array. Is there a way to only select 1 element of the array instead of all the values. When I do the select in t-sql for the linked server I get a semi-colon list of values.

The linked server db has a function called pro_element(fieldname,1,1) which will return 1 array element, but t-sql obviously won't recognize this. Is there something similar? Or can I tell t-sql to pass this to the DB even if it doesn't know the command?

Thanks for any help,
Scott

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-30 : 10:56:49

Array..?? how you are managing arrays in SQL Server.. ????
do you mean by table type data type????

Sucess Comes to those who Believe in Beauty of their Dream..

sbhegel
Starting Member

19 Posts

Posted - 2005-11-30 : 11:41:08

The array field is in the linked DB that is not a SQL server (It is called Progess).

When querying the field like such:
select cd_cmmt from pub.cd_det
where cd_ref = '6738703004'

I get the results:
;SIZE_D_C_T.itf;CA.itf;;;;;;;;;;;;

Where each array extent is separated by ;

I am wondering if there is a function in tsql to say give me entry 2 of cd_cmmt using ; as a delimiter.

Scott

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-30 : 12:03:59

No, you would have to write a user defined function to do this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-30 : 12:05:17

Happy Birthday


CREATE FUNCTION udf_GetWord (
	  @str nvarchar(4000)
	, @Word int
	, @Delim char(1)
)
RETURNS nvarchar(4000)
AS
BEGIN
	DECLARE @LastPosition int, @DelimFound int, @Start int, @End int, @WordFound nvarchar(4000)

	SELECT @LastPosition = 0, @DelimFound = 0
	WHILE (@DelimFound < @Word-1)
	  BEGIN
		IF (CHARINDEX(@Delim, @str, @LastPosition + 1) = 0)
			BREAK
	
		ELSE
		  BEGIN
			SET @LastPosition = CHARINDEX(@Delim, @str, @LastPosition + 1)
			SET @DelimFound = @DelimFound + 1
		  END
		
	  END
	
	SET @Start = @LastPosition + 1
	SET @End = CHARINDEX(@Delim, @str, @LastPosition + 1) - @Start
	IF (@End = 0) SET @End = LEN(@str)
	SELECT @WordFound = SUBSTRING(@str,@start,@end)
	RETURN @WordFound
END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sbhegel
Starting Member

19 Posts

Posted - 2005-11-30 : 12:48:33

WOW!

Thank you very much for that code. I will try it out in a bit.

I owe you one.

Thanks,
Scott

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-12-01 : 02:52:42

Well you can also check out following link if its help you..

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

Sucess Comes to those who Believe in Beauty of their Dream..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources