words of a string
-
Ok.... thought you'd gone nuts on me for a minute
Short naps get me through the day 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry guys. AS I was busy with my work I didn't check this forum for last few days.
The following is the procedure I find on the net.
Thanks for all your help.
CREATE FUNCTION dbo.fnSplitDelimited ( @sInputString1 VARCHAR(8000) , @sSplitChar CHAR(1))
RETURNS @tbl_List TABLE (Id1 varchar(100) )
AS
BEGIN
DECLARE @lInputStringLength1 Int ,
@lPosition1 Int ,
@lSplitChar1 Int ,
@lGroupID varchar(100)
SET @lInputStringLength1 = LEN ( @sInputString1 )
SET @lPosition1=1
SET @lSplitChar1=1
WHILE @lPosition1 <= @lInputStringLength1
BEGIN
SET @lSplitChar1 = CHARINDEX ( @sSplitChar , @sInputString1 , @lPosition1)
IF @lSplitChar1 = 0
BEGIN
SELECT @lGroupID = SUBSTRING( @sInputString1 , @lPosition1 ,1+ @lInputStringLength1 - @lPosition1)
SET @lPosition1= @lInputStringLength1 + 1
END
ELSE
BEGIN
SELECT @lGroupID =SUBSTRING( @sInputString1 , @lPosition1 , @lSplitChar1 - @lPosition1)
SET @lPosition1 = @lSplitChar1+1
END
INSERT @tbl_List( Id1)
VALUES ( @lGroupID)
END
RETURN
END
-
Sorry guys I have posted the wrong function.
Following is the correct one.
CREATE FUNCTION fn_get_few_words (
@stringToSplit varchar(3000),
@numberOfWords int
)
RETURNS varchar(3000) AS
BEGIN
DECLARE @currentword varchar(3000)
DECLARE @returnstring varchar(3000)
DECLARE @wordcount int
SET @wordcount = 0
SET @returnstring = ''
SET @currentword = ''
SET @stringToSplit = ltrim(rtrim(@stringToSplit))
Declare @index int
WHILE @wordcount < @numberOfWords AND len(@stringToSplit) > 0
BEGIN
Select @index = CHARINDEX(' ', @stringToSplit)
if @index = 0
BEGIN
SELECT @currentword = ltrim(rtrim(@stringToSplit))
SELECT @wordcount = @numberOfWords
END
else
BEGIN
IF (len(@stringToSplit) - @index > 0) BEGIN
SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string
SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest
END
END
SELECT @returnstring = @returnstring + ' ' + @currentword
SELECT @wordcount = @wordcount + 1
END
SET @returnstring = LTRIM(@returnstring)
RETURN @returnstring
END
-
I think it's time for one more... of each
. -
Sql_2005_fan
,
thanks u
for posting the function? Can it handle strings like
word1
,word2!word3#word4&&&&word5[SOMETEXT]word6()word7%%word8*(word9
From
what I c in the code, it cannot. To be abe to do this the function would have to be re-written (read: make it N times longer). But still even after that, it wouldn't handle the variable-length delimiter [SOMETEXT].
Sergiy,
i hope u can see better now why I have to resort to external libraries like .NET ones to be able to handle cases like this: it makes sense b/c it'
s easier to code/maintain. Yes u r absolutely right, that C# class (disguised as CLR function in the T-SQL code) can be applied to any text field in anywhere: excel, txt, access etc. I do it in SQL Server, b/c I work there: my job responsibilities include modifying data in the original (vendor) DBases of chemical data, and then rendering the datastructure of the DBases compatible with our JSP Web Software.
TO
summarize, my strategy is: do what u can by native T-SQL means; if stuck [see the splitting task above in the post: u cannot do it in pure T-SQL] get some help from really powerful programming environment like .NET.
What do u think?
What
is the weather like now in New Zealand? Always wanted to go there ..maybe some day
-
Sergei,
the split function Sql_2005_fan has posted here is not the best from split functions you can find on this forum.
I use the one which accepts ntext string (up to 2GB long) and nvarchar(20) delimiter (I limited length of delimiter to 20 probably because of lack of imagination
)Then to summarise your strategy:
make a difference:
"u cannot do it in pure T-SQL" means:
"it cannot be done in pure SQL" or
"you don't know how to do it in pure SQL" or
"the gain does not worth efforts, result you've got is not the best, but is acceptable for the client" - as you said "good enough for me".
Yes, "C# class can be applied to any text field" anywhere, trick is you not suppose to store data in BLOB fields.
Database is not a dump. The power of relational database is in sorting out data on arrival, not when you need it sorted.
Salesmans in any diary follow this rule and sort notes and coins when they PUT it into a cashier box, not when they need to give you change. Why? It must be more effective way. Would you agree?
I wonder - if salesmans are that smart or developers are that stupid?
I observed my latest warehouse project and realised that about 75% of my code for it relates to INSERT/UPDATE data in tables, and only 25% is about select. And I've been involved in this project because previous guy failed to provide required performance for SELECTs.
He could not realise that to improve SELECT he needs to work on INSERT.
P.S. Weather in NZ is perfect: +20 and 4th day without rain. Unbelievable as for spring time.
If you wanna come here choose a day in February. School holidays are over, motels are not booked, locals are out beaches, there are only those bloody american tourists over there
And it's hot and sunny that time in NZ.If you will need a road map you know whom to ask.
_____________
Code for TallyGenerator -
I'm still looking for the function that will split a 2gb string on a delimiter, Serqiy... any chance of you posting it or the URL you got it from? Thank you, Sir.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I finally found one at
http://www.sommarskog.se/arrays-in-sql.html
Is your's any better?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Don't know if mine is better, but I'm too lazy to deal with those slices.
Some advanced feachure are missed here, but it not gonna be a problem for you to restore it, if you need.
CREATE FUNCTION dbo.List_CharLong (
@List ntext, -- A delimiter-separated list of string values, this parameter may be up to 2GB in length
@Delim nvarchar(20),
@CharsToRemove nvarchar(50)
 
RETURNS @val TABLE (No int IDENTITY(1,1), Val nvarchar(400) )
AS
BEGIN
DECLARE @One tinyint SET @One = 1
DECLARE @TL int
SET @TL = DATALENGTH(@List) / 2
DECLARE @dl tinyint
SET @dl = DATALENGTH(@Delim) / 2
DECLARE @LeftPointer int, @RightPointer int, @SubStr varchar(50)
SET @LeftPointer = 0
-- Table containing symbols to be removed from returned values
DECLARE @Char2Remove TABLE (CharVal char(1) )
WHILE DATALENGTH(@CharsToRemove) > 0
BEGIN
INSERT INTO @Char2Remove (CharVal)
SELECT LEFT(@CharsToRemove, 1)
-- Remove taken symbol from original string
SELECT @CharsToRemove = REPLACE( @CharsToRemove, LEFT(@CharsToRemove, 1), space(0))
END
WHILE @RightPointer < @TL OR @RightPointer IS NULL
BEGIN
SELECT @RightPointer = CHARINDEX(@Delim, SUBSTRING(@List, @LeftPointer+@One, @TL) )
+ @LeftPointer - @One
IF @RightPointer <= @LeftPointer
SELECT @RightPointer = @TL
SELECT @SubStr = SUBSTRING(@List, @LeftPointer + @One, @RightPointer-@LeftPointer)
SELECT @SubStr = REPLACE(@SubStr, CharVal, space(0))
FROM @Char2Remove
INSERT INTO @val (Val)
SELECT @SubStr
SELECT @LeftPointer = @RightPointer + @dl
END
RETURN
END
GO
_____________
Code for TallyGenerator -
Very cool... thank you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply