asp classic Split data and transfer it to another table ...

Dear all Experts in ASP Classic

I am working with asp classic and msaccess db

I need a bit help for this script to be executed according my needs.

<%
set conn=server.CreateObject("adodb.connection")
conn.open "Provider=Microsoft.jet.oledb.4.0;data source="& server.MapPath("db\bd1.mdb")
set rs_main=server.CreateObject("adodb.recordset")

rs_main.open "select fileid,title,texts from add0",conn,1,2

do while not rs_main.eof
    d=rs_main("texts")
	title=RS_MAIN("title")
	dim p
    p=1
    for x=1 to len(d)
    if mid(d,x,1)="," or mid(d,x,1)="." or mid(d,x,1)="!" then
    texts=mid(d,p,x-p)
    p=x+1
    conn.execute("insert into table2 values ("&rs_main("fileid")&",'"&title&"','"&texts&"')")
    end if
next
rs_main.movenext
loop
rs_main.close
set rs_main=nothing
%>

Open in new window

Where:

Table name = Add0
fields
fileid (autonumerical)
title (text=100)
texts (Memo)

Table name = table2
fileid (autonumerical with duplicates)
title (text = 100 with duplicates)
texts (text = 100 with duplicates)

GOAL:

The script splits a long memo field into substrings splitted by "." or "," or "!", but not works adequately because sometimes a substring are larger than the width of the table2.texts

Do you know a way to change this script to split (1) the substrings in 10 words or (2) length (=100) avoiding to cut words or (3) each capital letter?

I would be grateful for any help..

Public Function split10Words(ByVal data As String) As String() Dim rx As Object Dim matches As Object Dim ret() As String Dim i As Long ReDim ret(0 To 0) Set rx = CreateObject("VBScript.Regexp") rx.Pattern = "(\S+)" rx.Global = True Set matches = rx.Execute(data) For i = 0 To matches.count - 1 ret(UBound(ret)) = ret(UBound(ret)) & matches(i) & " " If ((i + 1) Mod 10) = 0 Then ReDim Preserve ret(0 To UBound(ret) + 1) End If Next set matches = Nothing Set rx = Nothing split10Words = ret End Function

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 15

Fabrice LambertConsultingCommented: 2017-11-22

Maybe with a simple regex, and return the results as an array:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

BOB KUSPEAuthor Commented: 2017-11-23

Many Thanks

asp classic Split data and transfer it to another table ...

Who is Participating?