CRACK DATA IN ROWS - 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

Script Library

CRACK DATA IN ROWS

Author

Topic

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-18 : 08:38:08

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

22859 Posts

Posted - 2005-08-18 : 08:45:24

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-18 : 09:18:40

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

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-20 : 01:30:47

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources