Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: text separate problem

    Hi !
    I have a problem with text strings. I want to separate text that is between these | | signs. I need all the text in the sentense and there are about 6 this kind (|) of signs.

    Example:
    ;1;2a;2b;3;4a;4b|1|41201:005: 0190|Vaikeelamumaa, Lihula linn, Sook tn 193. Katastriyksus moodustatud plaani alusel.|1357 m2|1|Lepingu 27.11.1998 alusel osa avamisel sisse kantud 28.01.1999; ümber kirjutatud 05.03.2002. T.Sa

    The field is text type (255) and in sql 6.5
    I managed to export the column to sql 2000 if that helps.

    Any ideas how to do that?
    Thx!

  2. #2
    Join Date
    Nov 2003
    Posts
    2

    Smile

    drop function dbo.udf_Txt_SplitTAB


    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    GO

    CREATE FUNCTION dbo.udf_Txt_SplitTAB (

    @sInputList varchar(8000) -- List of delimited items
    , @Delimiter char(1) = ',' -- delimiter that separates items
    ) RETURNS @List table (Item varchar(8000))
    WITH SCHEMABINDING
    /*
    * Returns a table of strings that have been split by a delimiter.
    * Similar to the Visual Basic (or VBA) SPLIT function. The
    * strings are trimmed before being returned. Null items are not
    * returned so if there are multiple separators between items,
    * only the non-null items are returned.
    * Space is not a valid delimiter.
    *
    * Example:
    select * FROM dbo.udf_Txt_SplitTAB('abcd,123, 456, efh,,hi', ',')
    *
    * Test:
    DECLARE @Count int, @Delim char(10), @Input varchar(128)
    SELECT @Count = Count(*)
    FROM dbo.udf_Txt_SplitTAB('abcd,123, 456', ',')
    PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3
    THEN 'Worked' ELSE 'ERROR' END
    SELECT @DELIM=CHAR(10)
    , @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
    SELECT @Count = Count(*)
    FROM dbo.udf_Txt_SplitTAB(@Input, @Delim)
    PRINT 'TEST 2 LF :' + CASE WHEN @Count=2
    THEN 'Worked' ELSE 'ERROR' END
    *
    * © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
    * You may use this function in any of your SQL Server databases
    * including databases that you sell, so long as they contain
    * other unrelated database objects. You may not publish this
    * UDF either in print or electronically.
    * Published in T-SQL UDF of the Week Newsletter Vol 1 #29
    http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
    ************************************************** *************/
    AS BEGIN

    DECLARE @Item Varchar(8000)
    DECLARE @Pos int -- Current Starting Position
    , @NextPos int -- position of next delimiter
    , @LenInput int -- length of input
    , @LenNext int -- length of next item
    , @DelimLen int -- length of the delimiter

    SELECT @Pos = 1
    , @DelimLen = LEN(@Delimiter) -- usually 1
    , @LenInput = LEN(@sInputList)
    , @NextPos = CharIndex(@Delimiter, @sInputList, 1)

    -- Doesn't work for space as a delimiter
    IF @Delimiter = ' ' BEGIN
    INSERT INTO @List
    SELECT 'ERROR: Blank is not a valid delimiter'
    RETURN
    END


    -- loop over the input, until the last delimiter.
    While @Pos <= @LenInput and @NextPos > 0 BEGIN

    IF @NextPos > @Pos BEGIN -- another delimiter found
    SET @LenNext = @NextPos - @Pos
    Set @Item = LTrim(RTrim(
    substring(@sInputList
    , @Pos
    , @LenNext)
    )
    )
    IF LEN(@Item) > 0
    Insert Into @List Select @Item
    -- ENDIF

    END -- IF

    -- Position over the next item
    SELECT @Pos = @NextPos + @DelimLen
    , @NextPos = CharIndex(@Delimiter
    , @sInputList
    , @Pos)
    END

    -- Now there might be one more item left
    SET @Item = LTrim(RTrim(
    SUBSTRING(@sInputList
    , @Pos
    , @LenInput-@Pos + 1)
    )
    )

    IF Len(@Item) > 0 -- Put the last item in, if found
    INSERT INTO @List SELECT @Item

    RETURN
    END
    GO
    /*

    SELECT Item as [Stooge]
    FROM udf_Txt_SplitTAB( ';1;2a;2b;3;4a;4b|1|41201:005: 0190|Vaikeelamumaa, Lihula linn, Sook tn 193. Katastriyksus moodustatud plaani alusel.|1357 m2|1|Lepingu 27.11.1998 alusel osa avamisel sisse kantud 28.01.1999; ümber kirjutatud 05.03.2002. T.Sa', '|')

    */

    I got it!!!!!!!!!!!!!!!!

Posting Permissions

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