how can i split a recod in to new line

bcp5190
bcp5190 used Ask the Experts™
on
I have a temp table1 with only one field.
Each row may contain contain more than 200 character,
Now How can i split all records in to two if any record contain more than 200 character long.

I just want to add a new record next to existing one and split one record in to two.

I basically want to add new line if any of my record contains more than 200 character long.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
declare @newrecord varchar(2000)

while len(@newrecord ) >200
begin

Insert Into #mytemp
values(left(@newrecord,200))
select @newrecord =substring(@newrecord ,201,len(@newrecord )-200)

end
insert newtable
select left(field1,200)
GO

insert newtable
select substring (field1,201, len (field1) - 200)
where len (field1) > 200

GO
Sorry, I forgot the from clause,
add in the selects "from table1"
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

--if you want to split the record exactly in half.

declare @newrecord TABLE(col1 VARCHAR(400))

--For testing
--Insert record into this table here

Insert @newrecord(col1)
Select SUBSTRING(col1, ABS(LEN(col1)/2), LEN(col1))
from @newrecord
where len(col1) > 200

Update @newrecord
set col1 = SUBSTRING(col1, 1, ABS(LEN(col1)/2) - 1)
where len(col1) > 200

ADW
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
If you don't want to have to create a new table, you can do this to make the changes in your existing table:

BEGIN TRANSACTION  --optional

INSERT INTO table1
SELECT SUBSTRING(yourCol, 201, LEN(yourCol) - 200)
FROM table1
WHERE LEN(yourCol) > 200

UPDATE table1
SET yourCol = lEFT(yourCol, 200)
WHERE LEN(yourCol) > 200

COMMIT TRANSACTION

Author

Commented:
Hello gkern, this adds all larger records at end of all orinal records.
But instead if any record is > then 200 then i want to add it next to original record.

I basically want to create a two record instead of one but it has to be nest to orignal.

for example:
rowid HTML
------------
1  <html><body>
2  <i>only 150 character long</i>
3  <b>sadfdsafadsfadsfds .......ASDasdfa dfdafd</b>
4  <h1>only 150 character long</h1>
5   </body><html>

if third record is > then 200 character then output should be as follows
HTML
-----
1  <html><body>
2  <i>only 150 character long</i>
3  <b>sadfdsafadsfadsfds .......AS
4  Dasdfa dfdafd</b>
5  <h1>only 150 character long</h1>
6  </body><html>
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Unless you have a sequence number, or some other unique ascending value in the table, you can't guarantee the order anyway.

Do you have a sequence number?  If so, you can adjust the sequence number to do what you want.
There is a simple way but with overhead !!!

1. create new table
2. build a cursor
3. test each line
4. insert the records as needed


I wrote you a simple proc that get as a parameter the max size of rec you want and split it accordingly.


CREATE proc splitRows @fldSize int
as
set nocount on

begin
begin tran
declare @aRecord varchar (256)

declare splitRows_cur cursor local forward_only keyset read_only  for
select      field1
from table1


open splitRows_cur

fetch next from splitRows_cur
into      @aRecord

while @@fetch_status = 0
begin

      if (len (@aRecord) <= 200)
      begin
        insert newTable values (@aRecord)
      end
      else
      begin
     insert newtable values (left(@aRecord, @fldSize))
     insert newtable values (substring (@aRecord,@fldSize + 1, len (@aRecord) - @fldSize))
      end

fetch next from splitRows_cur
into      @aRecord
end


close splitRows_cur
deallocate splitRows_cur

commit tran

end

GKern


Now, the best is that in the new table you'll have a identity column that while building the table it will guarantee the order, something like the following

create table newTable (
  id int identity (1,1) primary key,
  myText varchar (256)
)


gkern

Author

Commented:
Thanks a lot

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial