| Author |
 Topic  |
|
|
kapilarya
Yak Posting Veteran
Canada
86 Posts |
 Posted - 08/18/2005 : 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
United Kingdom
22191 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 08/18/2005 : 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
Canada
86 Posts |
Posted - 08/20/2005 : 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
|
|
|
| |
Topic |
|
CRACK DATA IN ROWS
