none
HOW DO i CREATE A SUBSTR WHICH ENDS IN THE CHARACTER '@' AND IS OF VARYING LENGTH FROM ONE RECORD TO THE NEXT RRS feed

  • Question

  • this example is from a purchased list of auto repair shops, set up in a VFP9 database:

    website                                    email                                              busname

    joneslubeandmore.com              info@joneslubeandmor                     Jones Lube & More

    perryandterry.com                     customercare@perryan                     Friendly Auto Sales

    As can be clearly seen, the email field has been trimmed beyond repair, and needs to be restored by creating a substring of every letter to the left of the @ sign, and moved to the left of the website name: customercare@perryandterry.com, which, presumably is a correct and viable email address. So what is the command sequence that can be repeated through more than 24000 website+email listings?


    contact@ftlistmail.com

    Friday, February 15, 2019 2:50 AM

All replies

  • Your should know more about AT(), STRTRAN(), STUFF()

    lcEmail=EMAIL
    
    or
    
    lcEmail='info@joneslubeandmore.com'
    
    lnAtPos=AT('@',lcEmail)
    lcWebSite=STUFF(lcEmail,1,lnAtPos,'')
    
    ? lcWebSite

    Friday, February 15, 2019 4:52 AM
  • Hi ftlistmail.com

    if your data is part of VFP dbf, the you could repair the column with one line of code:

    REPLACE ALL email WITH GETWORDNUM( email , 1 , [@] ) + [@] + ALLTRIM( website ) IN crsDemo
    

    HTH


    Gruss / Best regards
    -Tom
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011

    Friday, February 15, 2019 7:06 AM
    Moderator
  • The answer from Tom will work, if the email field is long enough, so first alter that field to make it long enough.

    Or just limit the email data to the part before @ by replacing email with getwordnum(email, 1, '@') and then use alltrim(email)+"@"+ alltrim(website).

    Bye, Olaf.


    • Edited by OlafDoschke Friday, February 15, 2019 8:44 AM
    Friday, February 15, 2019 8:43 AM
  • About your original idea of varying length fields. VFP8 (I think) introduced varchar, at least they're available in VFP9, but in DBFs these are not stored in the FPT file, were variable length Memos and Blobs and other such field types are stored, they become part of the DBF with fixed length record size (RECSIZE()). And so a varchar(50) field takes 50 bytes, the only thing that's automatic with such field is trimming. It differs from alltrim() trimming though, because you can store leading and trailing spaces in varchar fields, too. The trimming is done with CHR(0) bytes.

    Don't go that route, these field types are mainly introduced for better fit of field behaviour when querying from remote data like MySQL or MSSQL Server, where these fields are actually variable length also in the low level file the data is stored persistently.

    It can also be convenient to have varchar fields in query results and you can use CAST() for that, but you'll not have an automatically fitting width of the max length, you need to either determine wha't necessary with MAX(LEN(expression)) or assume Varchar(254) will suffice for every case. You're wasting a bit of bytes, then, but only in RAM and only temporary.

    I still find it convenient enough to work with char and alltrim(), as that also removes whitespace accidentally entered by users, whereas varchar simply returns what you store, so either you need to alltrim before you store or still use alltrim on varchar fields. They're not magical or even useful, really, other than to map to remote data sources.

    Bye, Olaf.

    • Edited by OlafDoschke Friday, February 15, 2019 7:50 PM
    Friday, February 15, 2019 8:58 AM
  • Use StrExtract() to get the part before the @:

    emailname = STREXTRACT(email, '', '@')

    Tamar
    Friday, February 15, 2019 9:39 PM
    Moderator