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
 General SQL Server Forums
 Script Library
 CRACK DATA IN ROWS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/18/2005 :  08:38:08  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
THIS FUNCTION WILL HELP YOU CRACK DATA OF SINGLE COLUMN OR STRING INTO ROWS
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








Kapil Arya

Kristen
Test

United Kingdom
22431 Posts

Posted - 08/18/2005 :  08:45:24  Show Profile  Reply with Quote
Some documentation would be good please!

Is this a split function (like http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648) or something else?

P.S. If you put [code] ... [/code] around your code it will look better

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 08/18/2005 :  09:18:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Kapilarya,
I already asked you to explain the functionality of the script you give

When run this

Select * from CrackInRows(' ','this is for testing')

I get this error

Server: Msg 208, Level 16, State 1, Procedure CrackInRows, Line 15
Invalid object name 'IDNos'.


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kapilarya
Yak Posting Veteran

Canada
86 Posts

Posted - 08/20/2005 :  01:30:47  Show Profile  Click to see kapilarya's MSN Messenger address  Send kapilarya a Yahoo! Message  Reply with Quote
Hello Madhivanan,

Sorry, I forgot to give one table structure for this function.

CREATE TABLE [IDNos] (
[IDNos] [int] NOT NULL ,
CONSTRAINT [PK_IDNos] PRIMARY KEY CLUSTERED
(
[IDNos]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO IDNOS VALUES ( 1)
INSERT INTO IDNOS VALUES ( 2)
INSERT INTO IDNOS VALUES ( 3)
INSERT INTO IDNOS VALUES ( 4)
INSERT INTO IDNOS VALUES ( 5)
INSERT INTO IDNOS VALUES ( 6)
INSERT INTO IDNOS VALUES ( 7)
INSERT INTO IDNOS VALUES ( 8)
INSERT INTO IDNOS VALUES ( 9)
INSERT INTO IDNOS VALUES ( 10)
INSERT INTO IDNOS VALUES ( 11)
INSERT INTO IDNOS VALUES ( 12)
INSERT INTO IDNOS VALUES ( 13)
INSERT INTO IDNOS VALUES ( 14)
INSERT INTO IDNOS VALUES ( 15)

GO

You can add as many as no you require.




Kapil Arya
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.08 seconds. Powered By: Snitz Forums 2000