Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009

    Unanswered: Search string for key word followed by numeric value.

    I want to write a user-defined function. You pass in a string of text, usually a "sentence" of words separated by spaces. There may be multiple lines with CRLF between them.

    First I want to searrch the given string for a certain key word (or more than one, in case it's spelled different ways). If it's found, look at the following "word" to see if it's a numeric value (usually a 3 digit integer). It it is, the function should return a string of that numeric value.

    If the key word is not found, or is not followed by a number, the function should return a certain default string (or alternately, null).

    I mainly need to know some functions that will locate the key word, and then extract the following word.

    Is there a function that separates a string delimited with spaces into a string array?

  2. #2
    Join Date
    May 2005
    South Africa
    Provided Answers: 1
    You can try a combination of patindex and substring

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    create function ParseString(@String varchar(500), @Delimiter char(1))
    returns table
    --function ParseString
    --blindman, 5/20/2008
    --Parses a string based upon a given single-character delimiter,
    --without using loops or a tally table.
    --test parameters
    --declare	@String varchar(500)
    --declare	@Delimiter char(1)
    --set		@String = 'rnbqkbnr/pp1ppppp/8/2p5/4P3/5N2/PPPP1PPP/RNBQKB1R'
    --set		@Delimiter = '/'
    --set		@String = 'Abracadabra!, Shazam!, Presto!'
    --set		@Delimiter = ','
    with Results as
    		(select	1 as Ordinal,
    			ltrim(left(@String, charindex(@Delimiter, @String + @Delimiter)-1)) as StringValue,
    			convert(varchar(500), right(@String + @Delimiter, len(@String) - charindex(@Delimiter, @String+@Delimiter) + 1)) as Remaining
    		select	Ordinal+1,
    			ltrim(left(Remaining, charindex(@Delimiter, Remaining)-1)),
    			right(Remaining, len(Remaining) - charindex(@Delimiter, Remaining))
    		from	Results
    		where	len(Remaining) > 0)
    select	Ordinal,
    from	Results
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts