September 9, 2003 at 9:47 am
Ok,
I ran across this so I thought I would post it.
DECLARE
@TestString VARCHAR(100)
,@StringLen1 INT
,@StringLen2 INT
,@LengthOfStringRem INT
,@NumberOfTimes INT
SET @TestString = 'IAMHEREIAMTHEREIAMNOWHERE'
-- now get the number of times the word AM is in the string.
-- get starting length 25
SET @StringLen1 = LEN(LTRIM(RTRIM(@TestString)))
PRINT 'Full String Length ' + CAST(@StringLen1 AS VARCHAR(5))
-- get length minus string 19
SET @StringLen2 = LEN(LTRIM(RTRIM(REPLACE(@TestString,'AM',''))))
PRINT 'String Length After Replace ' + CAST(@StringLen2 AS VARCHAR(5))
-- now get the length of the string that was removed
SET @LengthOfStringRem = @stringLen1 - @StringLen2
PRINT 'Length of string removed ' + CAST(@LengthOfStringRem AS VARCHAR(5))
-- Ok now that you know 2 things
-- 1) After we replaced the word AM with '' the length went down by 6
-- 2) The length of the word that we care about is 2
-- so now you can figure out how many times the word AM was in the string
SET @NumberOfTimes = @LengthOfStringRem/2
PRINT 'The Number of times the string was removed ' + CAST(@NumberOfTimes AS VARCHAR(5))
Will
Edited by - womalley on 09/09/2003 09:51:33 AM
September 9, 2003 at 9:53 am
Nice. You might want to add this to the scripts section.
Steve Jones
September 9, 2003 at 10:21 am
quote:
Nice. You might want to add this to the scripts section.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
Thanks,
I will go ahead and add it over there..
Will
September 10, 2003 at 4:44 am
Nice thing... may I suggest that it would be even better if you modify it so that it works automatically with words of any length? As it is, one has to edit the number 2 (meaning length of string you are replacing) by the actual length in the last statement.
It could be something like this - I only added variable @SeekString and used it on several places in your code. This way, you only edit the SET @SeekString and the rest is done automatically.
DECLARE
@TestString VARCHAR(100)
,@StringLen1 INT
,@StringLen2 INT
,@LengthOfStringRem INT
,@NumberOfTimes INT
,@SeekString VARCHAR(100)
SET @TestString = 'IAMHEREIAMTHEREIAMNOWHERE'
SET @SeekString = 'HERE'
SET @StringLen1 = LEN(LTRIM(RTRIM(@TestString)))
PRINT 'Full String Length ' + CAST(@StringLen1 AS VARCHAR(5))
SET @StringLen2 = LEN(LTRIM(RTRIM(REPLACE(@TestString, @SeekString,''))))
PRINT 'String Length After Replace ' + CAST(@StringLen2 AS VARCHAR(5))
SET @LengthOfStringRem = @stringLen1 - @StringLen2
PRINT 'Length of string removed ' + CAST(@LengthOfStringRem AS VARCHAR(5))
SET @NumberOfTimes = @LengthOfStringRem/LEN(@SeekString)
PRINT 'The Number of times the string was removed ' + CAST(@NumberOfTimes AS VARCHAR(5))
September 10, 2003 at 5:25 am
quote:
Nice thing... may I suggest that it would be even better if you modify it so that it works automatically with words of any length? As it is, one has to edit the number 2 (meaning length of string you are replacing) by the actual length in the last statement.It could be something like this - I only added variable @SeekString and used it on several places in your code. This way, you only edit the SET @SeekString and the rest is done automatically.
DECLARE
@TestString VARCHAR(100)
,@StringLen1 INT
,@StringLen2 INT
,@LengthOfStringRem INT
,@NumberOfTimes INT
,@SeekString VARCHAR(100)
SET @TestString = 'IAMHEREIAMTHEREIAMNOWHERE'
SET @SeekString = 'HERE'
SET @StringLen1 = LEN(LTRIM(RTRIM(@TestString)))
PRINT 'Full String Length ' + CAST(@StringLen1 AS VARCHAR(5))
SET @StringLen2 = LEN(LTRIM(RTRIM(REPLACE(@TestString, @SeekString,''))))
PRINT 'String Length After Replace ' + CAST(@StringLen2 AS VARCHAR(5))
SET @LengthOfStringRem = @stringLen1 - @StringLen2
PRINT 'Length of string removed ' + CAST(@LengthOfStringRem AS VARCHAR(5))
SET @NumberOfTimes = @LengthOfStringRem/LEN(@SeekString)
PRINT 'The Number of times the string was removed ' + CAST(@NumberOfTimes AS VARCHAR(5))
I am glad to see that you added that. I am/was hoping that people would take this little piece of code and make it better. If you have any other suggestions on it please feel free to post them. I am new to this Forum but I can tell that I am going to have fun here..
Thanks
William O'Malley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply