Post reply

Spilt string into seperate fields

  • February 22, 2004 at 7:44 pm

    #107088

    Hi All

    I need some help in splitting a string from one field into 5 seperate fields. Both source and destination are in SQL Server 2000 sp3a.

    The source table is defined
[Account] varchar(20)
[Address] varchar(410)
    Destination is
[Account] varchar(10)
[Address1] varchar(82)
[Address2] varchar(82)
[Address3] varchar(82)
[Address4] varchar(82)
[Address5] varchar(82)
    Here's some sample data
067-114 MAIN ROAD;GLENORCHY TAS;;;
082-004 ;;;;
082-058 FLOOR 17;255 GEORGE STREET;SYDNEY NSW;;
082-100 LEVEL 20;500 OXFORD STREET;BONDI JUNCTION NSW;;
082-113 255 GEORGE STREET;SYDNEY NSW;;;
082-213 LEVEL 1;475-495 VICTORIA AVENUE;CHATSWOOD NSW;;
083-051 ;;;;
083-091 460 COLLINS STREET, MELBOURNE VIC 3000;;;;

    The seperator for the address field is the ';' character. I've tried using CHARINDEX, PATINDEX and the like, but T-SQL doesn't seem to recognise the ';' character. Probably because it's a terminator.

    Here's my script in which CHARINDEX returns 0,

    SELECT [account], CHARINDEX([address], ';'), LEFT([address], CHARINDEX([address], ';'))
FROM [address]
WHERE Account = '067-114'

    Anyone got any bright ideas?

     

    --------------------
    Colt 45 - the original point and click interface

  • February 22, 2004 at 8:32 pm

    #495078

    Phil,

    Prepare to kick thyself....

    it should be: CHARINDEX(';', [address])

     


    Cheers,
    - Mark

  • February 22, 2004 at 9:21 pm

    #495082

    How on earth can this be done with CHARINDEX?

    CHARINDEX will only Return the starting position of the specified expression in a character string, which will be a number!

    Kindest Regards,

  • February 22, 2004 at 9:30 pm

    #495084

    I think it's just an example.  Phil will be combining results of charindex with other text handling functions (eg LEFT, as shown in his other example).


    Cheers,
    - Mark

  • February 23, 2004 at 12:39 am

    #495094

    Do you use VB?

    ? instr(1,"123","2",vbTextCompare )

  • February 23, 2004 at 12:48 am

    #495095

    mccork, thanks. Now maybe I'll get something to work.

    5409045121009, I'll need to do this for 300k+ records so T-SQL is definately a preference

     

    --------------------
    Colt 45 - the original point and click interface

  • February 23, 2004 at 2:01 am

    #495102

    For interested parties, if anyone has a nicer solution feel free to squawk. 

    SELECT 
 Account 
 , Address
 , REPLACE(LEFT([Address], CHARINDEX(';', [Address])
   ), ';', '') as Address1
 , REPLACE(SUBSTRING([Address]
   , CHARINDEX(';', [Address])
   , CHARINDEX(';', [Address], CHARINDEX(';', [Address]) + 1) 
    - (CHARINDEX(';', [Address]))
   ), ';', '') as Address2
  , REPLACE(SUBSTRING([Address]
   , CHARINDEX(';', [Address], CHARINDEX(';', [Address]) + 1)
    , CHARINDEX(';', [Address], CHARINDEX(';', [Address]
    , CHARINDEX(';', [Address]) + 1) + 1) 
    - (CHARINDEX(';', [Address], CHARINDEX(';', [Address]) + 1))
   ), ';', '') as Address3
  , REPLACE(SUBSTRING([Address]
   , CHARINDEX(';', [Address], CHARINDEX(';', [Address]
    , CHARINDEX(';', [Address]) + 1) + 1)
     , CHARINDEX(';', [Address], CHARINDEX(';', [Address]
      , CHARINDEX(';', [Address]) + 1) + 1) + 1
     - (CHARINDEX(';', [Address], CHARINDEX(';', [Address]) + 1) + 1)
   ), ';', '') as Address4
 , REPLACE(REVERSE(LEFT(REVERSE([Address])
   , CHARINDEX(';', REVERSE([Address]), 2))
   ), ';', '') as Address5
from dbo.AddrSrc

     

    --------------------
    Colt 45 - the original point and click interface

  • February 23, 2004 at 12:24 pm

    #495205

    Assuming there may be periods but no tildes or carets in the data:

    SELECT Account, Address, LEFT(Address,CHARINDEX(';',Address)-1) Address1,

    REPLACE(REPLACE(PARSENAME(REPLACE(REPLACE(STUFF(Address,1,CHARINDEX(';',address),''),'.','~'),';','^.^'),4),'^',''),'~','.') Address2,

    REPLACE(REPLACE(PARSENAME(REPLACE(REPLACE(STUFF(Address,1,CHARINDEX(';',Address),''),'.','~'),';','^.^'),3),'^',''),'~','.') Address3,

    REPLACE(REPLACE(PARSENAME(REPLACE(REPLACE(STUFF(Address,1,CHARINDEX(';',Address),''),'.','~'),';','^.^'),2),'^',''),'~','.') Address4,

    REPLACE(REPLACE(PARSENAME(REPLACE(REPLACE(STUFF(Address,1,CHARINDEX(';',Address),''),'.','~'),';','^.^'),1),'^',''),'~','.') Address5

    FROM AddrSrc



    --Jonathan

  • February 23, 2004 at 12:49 pm

    #495211

    Phillcart, sorry I was referring to the fact that with instr the parameters are 'really' switched around. Something I am running into myself between TSQL and VB/VbScript.

     

     

  • February 23, 2004 at 1:52 pm

    #495217

    Phill,

    Some inline views may help simplify it:

    select
    Account,
    Address1 = substring(address, 1, delimpos1-1),
    Address2 = substring(address, delimpos1+1, delimpos2-delimpos1-1),
    Address3 = substring(address, delimpos2+1, delimpos3-delimpos2-1),
    Address4 = substring(address, delimpos3+1, delimpos4-delimpos3-1),
    Address5 = substring(address, delimpos4+1, address_len-delimpos4)
from (
    select *, delimpos4=charindex(';', address, delimpos3+1)
    from (
        select *, delimpos3=charindex(';', address, delimpos2+1)
        from (
            select *, delimpos2=charindex(';', address, delimpos1+1)
            from (
                select *, address_len=len(address), delimpos1=charindex(';', address)
                from AddrSrc
            ) [Inline1]
        ) [Inline2]
    ) [Inline3]
) [Inline4]

     

     


    Cheers,
    - Mark

  • February 23, 2004 at 10:20 pm

    #495240

    Jonathan, interesting use of PARSENAME, I'll have to remember that one. would I be correct in saying that it doesn't work if you have 6 or more data elements to extract?

    Mark, yes that does look much simpler.

    For interested parties, here are the stats for each method. The select was part of an insert statement which massaged the data into a second table. This was done on a stand-alone SQL 2000 server that only I use.

    Marks Method

    Table 'address_tmp'. Scan count 0, logical reads 292108, physical reads 0, read-ahead reads 0.
Table 'address'. Scan count 1, logical reads 3117, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 54156 ms,  elapsed time = 56803 ms.
    Network Statistics
  Number of server roundtrips 3
  Number of TDS packets sent 3
  Number of TDS packets received 6
  Number of bytes sent 1866
  Number of bytes received 14012
Time Statistics
  Cumulative client processing time 12
  Cumulative wait time on server replies 2 
    Jonathans Method
    Table 'address_tmp'. Scan count 0, logical reads 292108, physical reads 0, read-ahead reads 0.
Table 'address'. Scan count 1, logical reads 3117, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 48719 ms,  elapsed time = 49142 ms.
    Network Statistics 
  Number of server roundtrips 3
  Number of TDS packets sent 3
  Number of TDS packets received 5
  Number of bytes sent 1524
  Number of bytes received 9900
  
Time Statistics 
  Cumulative client processing time 43
  Cumulative wait time on server replies 0
    My Method
    Table 'address_tmp'. Scan count 0, logical reads 292108, physical reads 0, read-ahead reads 0.
Table 'address'. Scan count 1, logical reads 3117, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
 CPU time = 50485 ms,  elapsed time = 51641 ms.
    Network Statistics
  Number of server roundtrips 3
  Number of TDS packets sent 3
  Number of TDS packets received 6
  Number of bytes sent 2402
  Number of bytes received 12914
Time Statistics
  Cumulative client processing time 5
  Cumulative wait time on server replies 0

     

    --------------------
    Colt 45 - the original point and click interface

  • February 24, 2004 at 4:09 am

    #495269

    SET QUOTED_IDENTIFIER off

    declare @add1   varchar(31),

     @add2   varchar(31),

     @add3   varchar(31),

     @add4   varchar(31),

     @add5   varchar(31),

     @fld  varchar(200)

    /*

    rec1 MAIN ROAD;GLENORCHY TAS;;;

    rec2 ;;;;

    rec3 FLOOR 17;255 GEORGE STREET;SYDNEY NSW;;

    rec4 LEVEL 20;500 OXFORD STREET;BONDI JUNCTION NSW;;

    rec5 255 GEORGE STREET;SYDNEY NSW;;;

    rec6 LEVEL 1;475-495 VICTORIA AVENUE;CHATSWOOD NSW;;

    rec7 ;;;;

    rec8 460 COLLINS STREET, MELBOURNE VIC 3000;;;;

    */

    select @fld = 'MAIN ROAD;GLENORCHY TAS;;;0001'

    while PATINDEX ( '%;%' , @fld ) > 0

    begin

    select 'add part to remove - ',left(@fld,PATINDEX ( '%;%' , @fld )-1)

    select @fld = substring(@fld,  PATINDEX ( '%;%' , @fld )+1, len(@fld))

    select 'new add fld', @fld

    end

    select 'final add portion - ',@fld

  • February 24, 2004 at 4:50 am

    #495271

    It doesn't work (without tweaking; notice I chopped the first piece off) unless you have four or fewer pieces to extract.  PARSENAME() is also limited to 128 characters per piece and cannot work with zero length pieces, which is why I used the carets.



    --Jonathan

  • February 24, 2004 at 5:11 am

    #495273

    Try creating the function below.  It accepts a delimited string + the delimeter definition as input and returns a table / recordset with the values contained within the delimeted string split into data rows.  I tried it with the sample data provided and it split it without any problems.

    Use it from within T-SQL or VB, etc as follows:

    select * from fn_split( 'abc+def+ghi+jkl+mno+pqr+stu+vwx+yz' , '+')

    -- Reuben

     

    CREATE FUNCTION dbo.fn_split ( @p_sDelimitedText nvarchar(1000), @p_sDelimiter nvarchar(1) )

    RETURNS @ReturnTable TABLE

    (

    ReturnCol nvarchar(1000)

    )

    AS

    BEGIN

    DECLARE @iPos smallint,

     @sStringBuild nvarchar(1000),

     @sByte nchar(1)

    SET @iPos = 1

    SET @sStringBuild = ''

    WHILE @iPos <= LEN(@p_sDelimitedText)

    BEGIN

     SET @sByte = SUBSTRING(@p_sDelimitedText, @iPos, 1)

     IF @sByte = @p_sDelimiter

     BEGIN

      IF LEN(@sStringBuild) > 0 INSERT @ReturnTable SELECT @sStringBuild

       SET @sStringBuild = ''

     END

     ELSE

     BEGIN

      SET @sStringBuild = @sStringBuild + @sByte

     END

     SET @iPos = @iPos + 1

    END

    IF LEN(@sStringBuild) > 0 INSERT @ReturnTable SELECT @sStringBuild

    RETURN

    END

    GO

  • February 24, 2004 at 1:07 pm

    #495407

    Nice solution above. I like the use of return tables.

    Here is an interesting variation that allows for a large number of parameters based on XML

    declare @temp varchar(5000)

    --set @temp = 'FLOOR 17;255 GEORGE STREET;SYDNEY NSW;;'

    set @temp = 'LEVEL 20;500 OXFORD STREET;BONDI JUNCTION NSW;;'

    DECLARE @idoc int

    DECLARE @doc varchar(1000)

    SET @doc ='

    <ROOT>

    <A>'

    + Replace(@temp, ';', '</A><A>')

    + '</A></ROOT>'

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    SELECT myid / 2 as id, replace(substring(contactname,4,1000),'</A>','')

    FROM OPENXML (@idoc, '/ROOT/A',1)

          WITH (myid int '@mp:id',

    ContactName varchar(50) '@mp:xmltext')

    EXEC sp_xml_removedocument @idoc

     

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply