Skip to main content
D
DDhillon
Known Participant
September 9, 2006
Question

Splitting and storing data from list into db

  • September 9, 2006
  • 1 reply
  • 213 views
I would like to separate the phone number from address in list look like below. 604-530-6545 is phone # and 19500 Langley By-Pass is address. The % sign splits the records. I want phone to store in one field and address in another. Is there a way it could be done?

%604-530-6545
19500 Langley By-Pass
%604-574-0411
5640 188 Street
%604-538-8880
3550 King George Highway
%604-877-1351
601 Stamps Landing
%604-580-2600
12350 Pattullo Place
%604-946-7717
4856 48 Avenue
%604-263-2121
2112 41st Avenue West
%604-532-8835
20488 Fraser Highway
    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    September 9, 2006
    If the phone number is always in the format you show, you can always do something like this:

    insert into yourtable
    (phone, address)
    <cfloop list = yourlist, index = "ThisItem" delimters="%">
    select distinct '#left(ThisItem(12)#', '#right(ThisItem(len(ThisItem) - 12)#'
    from somesmalltable
    <cfif listfind(ThisItem) is listlen(YourList)>
    union
    </cfif>
    </cfloop>

    Make sure each list item is unique before trying this though, otherwise you'll probably crash.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices