Solved

Ltrim variable characters from string

Posted on 2004-10-23
856 Views
Last Modified: 2010-05-18
Hi,

I need to Ltrim a group of characters from a string using as my selection, characters stored in a variable. I am doing this for multiple records in a recordset and storing the result to a field in a table.

The fields: ID (can vary in length), LinkCodeFM and FMCode

Example:

ID=11
LinkCodeFM=11-1C2

I want to save just "1C2" into a new field called FMCode, I have stored the ID into a variable mID as this changes from record to record. So I want to Ltrim what is contained in the variable "ID" & "-" from LinkCodeFM and store the result in FMCode. The catch is the ID can be 1, 2 or 3 characters long, so a simple Ltrim won't work. Can you use variable contents to a Ltrim a string? I look forward to your help...
0
Question by:mitch87
    4 Comments
     
    LVL 8

    Expert Comment

    by:sigmacon
    update YOUTR_TABLE_NAME_HERE
    set FMCode = (
              case when charindex('-', LinkCodeFM) > 0
                    then right(LinkCodeFM, len(LinkCodeFM) - charindex('-', LinkCodeFM) - 1)
                    else null
              end
          )

    Explanation: This updates the FMCode column for ALL rows in the table. If the LinkCodeFM has a dash (checked by charindex('-', LinkCodeFM) > 0) then it sets it to remaining string after the dash, otherwise to NULL
    )
    0
     
    LVL 8

    Expert Comment

    by:sigmacon
    SORRY - PLEASE IGNORE MY POST - ITS FOR SQL SERVER - I AM SOOO SILLY!
    0
     
    LVL 41

    Accepted Solution

    by:
    UPDATE MyTable SET FMCode=Mid$([LinkCodeFM],Len([ID])+1)
    0
     

    Author Comment

    by:mitch87
    Thanks Shane...I used your code, and needed to tweak the last portion. The FMCode was including the dash, ex: -1C2. So I changed the location where the string begins to Len[ID]+2. This eliminated the dash from the string. Thanks!
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now