dcsimg
Results 1 to 9 of 9

Thread: Taking the number from a string

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    281

    Taking the number from a string

    Hello
    I have been given an access database which within one of the tables has a Text field with sale prices in the format of £12345 but i need to check the number part of that text to see if it is higher or lower than a given number.

    How would you write that without changing the field to a number ?

    Thank you.

  2. #2
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,000

    Re: Taking the number from a string

    A quick test shows this works:

    Code:
    SELECT * from table1 WHERE replace(price,'£','')>200
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  3. #3
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    49

    Re: Taking the number from a string

    Quote Originally Posted by dee-u View Post
    A quick test shows this works:

    Code:
    SELECT * from table1 WHERE replace(price,'£','')>200
    I like that - I'll use it. Thanks. Works fine on MS SQL Server - but the original question mentioned "Access". I couldn't get the REPLACE syntax working on JET/OLEDB

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    281

    Re: Taking the number from a string

    Thank you, But, as Axcontrols2 says, I cant getting working on Access. Does anybody know of an alternative ?

  5. #5
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Candon City, Ilocos Sur, Phils.
    Posts
    11,000

    Re: Taking the number from a string

    This is an example for VB6, I just used "PROVIDER=Microsoft.ACE.OLEDB.12.0;" instead of Jet. If the requirement is to use Jet then I guess it's time to find other solutions, like just retrieving all the records and manually check its value?

    Code:
      db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=G:\test.mdb;"
    
      Set adoPrimaryRS = New Recordset
      adoPrimaryRS.Open "select aa,Price from test WHERE replace(price,'£','')>400", db, adOpenStatic, adLockOptimistic
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    49

    Re: Taking the number from a string

    Quote Originally Posted by thingimijig View Post
    Thank you, But, as Axcontrols2 says, I cant getting working on Access. Does anybody know of an alternative ?
    If you're stuck using JET then even if there was a way to strip or ignore the currency symbol I kind of suspect that JET would still do a string compare and you wouldn't get the results you want.

    The field is text so <LessThan would compare strings "100", "200", "300", "1000" would return them in the order "100", "1000", "200", "300" because, in a string compare, "1000" is less than "200".

    If you just have a small number of records in the table you could load the whole table into a datatable or datagrid and sort/filter in memory and chuck away the records you don't want. Functions like Replace work great on SQL Server because there's a dedicated process doing the work in the same machine as the server. With JET though, if such a function existed, it would be the jet engine doing the work in the application anyway (there's no server) so it's probably just as quick to load the whole thing into memory and sort/filter it yourself.

    But this was me I think I'd add a column to the table and use that to store the numerical value. Might take a couple of lines of code to make sure the columns are sync'd but it's a one time hit and thereafter you'll have fast numerical sorting.
    Last edited by Axcontrols2; Jul 15th, 2019 at 05:47 AM.

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Aug 2003
    Location
    Wigan, UK
    Posts
    281

    Re: Taking the number from a string

    Thank you for your time and experience, I see the issue with the string comparison. I think i will load all the results and filter them.

    Thanks

  8. #8
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: Taking the number from a string

    tested with access 2003 :
    just changed the field-type from text to numeric
    and access did remove the prefixes automaticly
    do not put off till tomorrow what you can put off forever

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,260

    Re: Taking the number from a string

    I kind of suspect that JET would still do a string compare and you wouldn't get the results you want
    This would have been a problem with the SQL Server version too. Unless it's explicitly cast to a numeric type it will be treated as a string.

    I'm no expert in JET (haven't looked at it in well over a decade) but I had a quick google on how to do a replace. The best solution seems to be to use Mid to get a substring which would look something like: MID(Price, 2, LEN(Price) - 1). But you'd still want to cast it to a numeric before doing the actual comparison.

    I'd also back up AxControl on having the numeric values in their own columns if you're able to affect the design. In fact, I'd just remove the £ and change the existing column to numeric as IkkenGij has done. The £ symbol is presentation, not data, and doesn't belong in the database at all.
    Last edited by FunkyDexter; Jul 16th, 2019 at 07:24 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width