Results 1 to 13 of 13

Thread: Split long text

  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Split long text

    Good day,

    I have a database table that is populated by bulk insert of a text file. This file is not comma delimetated and there are no line end characters, just one very long string, although the file is fixed length.

    As I said I now have this long string in my SQL Server 2005 table. Is there a fast way to split this up into multiple rows?

    Say for instance I have a string of 100 characters and each row would be made up of 10 characters. Is there a easy way to split this into the 10 rows or 10 characters using just a SQL statement?

    Please can someone help.
    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,

    It certainly can be done.

    Quote Originally Posted by plsh
    ...Is there a easy way to split this into the 10 rows or 10 characters using just a SQL statement?
    Quote Originally Posted by Somebody else
    Oh geez, didya hafta?
    And indeed, it can be done using a single SQL statement, by using the mod operator and the substring function.

    That will be 250 pounds please.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    Something like this perhaps. The only limitation is that max 32767 row can be returned. And it only works on SQL Server 2005.

    Code:
    declare @text varchar(100)
    
    set @text='abcdefghijklmnopqrstuvwxyz1234567890';
    
    with cte as
    (
    	select case when len(@text)>10 then substring(@text,1,case when len(@text) >10 then 10 else len(@text) end) else @text end as txt,case when len(@text)>10 then substring(@text,11,len(@text)-10) else null end as rest
    	union all 
    	select case when len(rest)>10 then substring(rest,1,case when len(rest) >10 then 10 else len(rest) end) else rest end as txt,case when len(rest)>10 then substring(rest,11,len(rest)-10) else null end as rest from cte
    	where not rest is null
    )
    select txt from cte option(maxrecursion 0)

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    And it only works on SQL Server 2005.
    And for those who are not using SQL Server 2005?

    You can solve this by using a numbers table. The substring function is used to extract the string fragments using the offsets which are determined by the mod function.

    Regards,
    Last edited by r123456; 11-20-07 at 06:32.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    And of course as the alert reader will point out, you can substitute the read from the Numbers table with a just as trivial While loop implementation. Though I should duly point out your express desire to use only an SQL statement

    using just a SQL statement?
    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Ok now you have lost me. The number of characters in the file is 5,218,865, no row terminators no delimitation as I said before. Each record is actually made up of 180 characters. So there should be about 29,000 seperate rows from this very long string. How would the mod statement be used then?

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Yes, the number of characters is certainly an important piece of information to be considered in choosing the most appropriate. Although more elegant, I would avoid the pure SQL solution on this occasion and just use the While loop. You would still use the mod and substring functions, just this time in the context of a While loop rather than a table read.

    Regards,
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    And there is no way of doing it with a formatfile on the bulk insert?

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    That I do not know, for I have not used bulk insert. Though if in using the bulk insert you can execute an SQL Script, then you certainly can do it.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Managed to sort it out, use a formatfile something like this:
    9.0
    1
    1 SQLCHAR 0 180 "" 1 Fieldname SQL_Latin1_General_Cp437_BIN

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by plsh
    Ok now you have lost me. The number of characters in the file is 5,218,865, no row terminators no delimitation as I said before. Each record is actually made up of 180 characters. So there should be about 29,000 seperate rows from this very long string. How would the mod statement be used then?
    I think, in the long run, it would just be easier to shoot the person sending you this file. I'm going to guess it is coming from a State Government agency. Am I right?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Nope it comes from our ERP system and they can't / wont change the output file. Not too serious I guess as I did manage to make a plan, just irratating. They feel they have done their job by giving me the file but how easy it is to use is my problem. What can you do I guess......

  13. #13
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Code:
    CREATE PROC myProc AS
    @split AS integer
    BEGIN
    DECLARE @len bigint, DECLARE @pos bigint
    
    SET @len = "SELECT LEN(fld) FROM tbl"
    
    SET @pos = 1
    
    WHILE (@pos <= @len)
    	BEGIN
    		INSERT INTO newTbl FROM
    			(SELECT SUBSTRING(fld, @pos, @split)
    		SET @pos = @pos + @split
    	END
    END
    Code:
    EXEC myProc
    	@split = N'180'
    I wrote it on the fly ... I didn't test it.


    Hope it can helps.
    Last edited by ortho; 11-21-07 at 15:55.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

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