ParseName.. - 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

SQL Server 2000 Forums

Transact-SQL (2000)

ParseName..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-23 : 05:09:10

Hello Everyone..

We have the text columns which stores the data in the following Manner..
ReqNo
1@2@3@4@5

Currently we have function ..


 CREATE FUNCTION [dbo].[CrackInRows] (@delim varchar(1), @Phrase2Crack as varchar(8000))  
RETURNS @CrackRow table   
(  
 INROWS varchar(1000)  
)  
as  
BEGIN   
insert @CrackRow  
Select NullIf(SubString(@Delim + @Phrase2Crack + @Delim , IDNos ,   
CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos) , '') AS INROW  
FROM IDNos  
WHERE IDNos <= Len(@Delim + @Phrase2Crack + @Delim) AND   
SubString(@Delim + @Phrase2Crack + @Delim , IDNos - 1, 1) = @Delim   
AND CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos > 0  
return  
END

this was the function which we are currently using .. for parsing ..

but there is limitation to this function that if i directly pass the column name instead of the data.. then its returns an error

somthing like this..

Select * From Dbo.CrackInRows('@',columnName)

Then its gives me an error.

So i was just thinking to modify the function or to write all to gather the new function..

Any Better Ideas for the same..

Thanks..

Complicated things can be done by simple thinking

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-23 : 05:52:18

You can also create a function which accepts the string as well as the separator and return a table. Then you can include this table in your in statement.

For your help, You can check this inline function below.

CREATE Function fnCsvToTABLE (@Array varchar(8000), @separator varchar(5))
returns @IntTable table
(IntValue VARCHAR(8000))
AS
begin

declare @separator_position int
declare @array_value varchar(1000)

set @Array = @Array + ','

while patindex('%'+@separator+'%' , @array) <> 0
begin

select @separator_position = patindex('%'+@separator+'%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

This will return a table.

So your query will be

select * from property Where PropertyLocationID IN (Select IntValue from fnCsvToTABLE(@CSV_String,',')

Regards
Sachin Samuel

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-23 : 06:23:52

quote:
Originally posted by sachinsamuel

CREATE Function fnCsvToTABLE (@Array varchar(8000), @separator varchar(5))
returns @IntTable table
(IntValue VARCHAR(8000))
AS
begin

declare @separator_position int
declare @array_value varchar(1000)

set @Array = @Array + ','

while patindex('%'+@separator+'%' , @array) <> 0
begin

select @separator_position = patindex('%'+@separator+'%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

This will return a table.

So your query will be

select * from property Where PropertyLocationID IN (Select IntValue from fnCsvToTABLE(@CSV_String,',')

Regards
Sachin Samuel

Thanks for the reply ..

but the function which you specifed does the same things as the function which i have the posted in my post..

what i want is somthing like this ..

Select * FRom Dbo.CsvFunction(Select Column_Name From Tabname, '@')

somthing like this which will get the all the data from the column sperated by '@' in the table format...

Any Help..

Complicated things can be done by simple thinking

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-23 : 06:35:20

Then what is the error u getting?

Regards
Sachin

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-23 : 06:44:19

See if i run this query ..
select * From Dbo.fnCsvToTABLE('1@2@','@')

its gives me the proper output..
i.e.
1
2

But if run the following thing then its gives me the error..

Select * From Dbo.fnCsvToTABLE((Select DisplayFieldPrefix From WrkFlowMast),'@')

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '('.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.

Any Clue..

Complicated things can be done by simple thinking

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 06:55:25

You can't pass a resultset into a function.

What are you wanting as output? If two rows in WrkFlowMast both have "1@2@" do you want those values returned multiple times or not?

How many rows in WrkFlowMast ? ("Lots" or "Very few")?

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-23 : 07:07:39

oks.. this is how the data is there in the workflowmst.

DisplayFieldPrefix
1@2@3@4@
A@B@C@
C@D@F@

I want the output somthing like this
1
2
3
4
A
B
C
D
E
F

So that then i can join this ouput with some table.. rather then using the looping ..

Currently there are 80 Rows and it can go upto 300 rows maximum..

Yeah if its repeating then too i want to get that value. nothing like distinct ..

Hope this makes any sense..

Complicated things can be done by simple thinking

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 07:22:38

Well, if DisplayFieldPrefix cannot average longer than 26 characters I would go with:


DECLARE @MyString varchar(8000)
SELECT @MyString = COALESCE(@MyString, '') 
                 + DisplayFieldPrefix
                 + CASE WHEN DisplayFieldPrefix NOT LIKE '%@'
                        THEN '@' -- Append "@" delimiter
                        ELSE ''
                        END
FROM WrkFlowMast

select * From Dbo.fnCsvToTABLE(@MyString,'@')

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-23 : 07:52:59

I think with this it will truncate the value .. :-( in the variable MyString and more over the DisplayFieldPrefix is also TextColumn..

see how its used in our application .

This Column contain the rights of the user so its actual data would be somthing like this
--------------------------------------------------------------------------------------------------------------------------------
UNDEF@WP0001@WP00011@WP0002@WP0003@WP0004@WP0005@WP0006@WP0007@WP0008@WP0009@WP0010@WP0011@WP0012@WP0013@WP0014@WP0015@WP0016@WP0017@WP0018@WP0101@WP0019@WP0020@WP0021@WP0022@WP0023@WP0024@WP0025@WP0026@WP0027@WP0028@WP0029@WP0030@WP0031@WP0032@WP000111@WP0033@WP0034@WP0035@WP0036@WP0037@WP0038@WP0039@WP0040@WP0041@WP0042@WP0043@WP0044@WP0045@WP0046@WP0047@WP0048@WP0050@WP0053@WP0054@WP0055@WP00921@WP00092@WP00093@WP00094@WP00095@WP00096@WP0056@WP0057@WP0058@WP0059@WP0060@WP0061@WP0062@WP0063@WP0064@WP0065@WP0066@WP0067@WP0068@WP0069@WP0070@WP0071@WP0072@WP0073@WP0074@WP0075@WP0077@WP0078@WP0082@WP0083@WP0084@WP0085@WP0086@WP0087@WP0088@WP0089@WP0090@WP0091@WP0092@WP0093@WP0094@WP0095@WP0096@WP0097@WP0098@WP0099@WP0100@WP00101@WP00102@WP00103@
-------------------------------------------------------------------------------------------------

This is the record of on column .. :-(

Any other clue..

Complicated things can be done by simple thinking

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 08:02:36

Well that wasn't clear from your example.

You will have to cursor around the rows in WrkFlowMast and use a TEXT data type splitter to split each row. (See the "Frequently Given Answers" topic in the "New to SQL Server" forum - look for CSV and SPLITTING - there is a link in that thread to a splitter for TEXT datatype.

However, I reckon it would be much better if you stored this data in a more normalised table as it is "arrives"

Kristen

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-23 : 08:27:31

Function can be used in a query, but cannot take query as parameter. In short you can use this as co-related query.

regards
Sachin

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-23 : 08:32:56

well.. i wished i could normalize the things but its the base of the system..
and i cant now.. since the package is used since 2000 ..and to change the accordingly in VC++ its really the hell of the job..

so well need to find some round about .. :-(..

Thanks for the help ..

Really Appreciated the same.

Complicated things can be done by simple thinking

Kristen
Test

22859 Posts

Posted - 2005-10-23 : 10:41:15

"so well need to find some round about"

You could have a trigger on WrkFlowMast so that any changes to DisplayFieldPrefix were "duplicated" in a normalised "mirror table". The two would be identical, so you could just use the normalised table when you wanted to, and the all the existing "legacy" stuff could carry on using the DisplayFieldPrefix column, as it did before.

To "split" a TEXT datatype, from a table with multiple rows, is going to be horribly slow. If you only do it once in a while I suppose that's OK, but otherwise you would be better with a "mirror" table.

Kristen

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-25 : 03:13:04

Sorry was on not in office yesterday..

Well thatz the great option.. for normalizing .. but I was anlysing the same and got the conclusion that change in what i want.. will be affected at the lots of place so was will not suit for me..

The stuff wouldnt be use for often . but yaa.. can be used too depending the clients requirement..

for the time being i open using split only ..

neways Thanks for the help .. will certainly try to do it..

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources