SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 MS Access
 replace in MS Access sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Controller1
Starting Member

8 Posts

Posted - 08/18/2013 :  04:54:05  Show Profile  Reply with Quote
i need here is a querry that REPLACE K, F, BG , and #* from TRX_REF column

My data is below
T100007272
Z100009796
XY87069498
87431954 #4799
87431924 #699
87431910 #1019
87431884 #93
87431877 #28
87431849 #139
87431812 #7007
87431807 #137
87431718 #60

jethrow
Starting Member

USA
37 Posts

Posted - 08/18/2013 :  05:07:31  Show Profile  Reply with Quote
Could you provide more information? I don't see a column TRX_REF, nor do I see any K, F, or BG. Do you want to replace the #* - as in #4799 - with something else, or just remove them?

Microsoft SQL Server Noobie
Go to Top of Page

Controller1
Starting Member

8 Posts

Posted - 08/18/2013 :  07:39:26  Show Profile  Reply with Quote
Thanks, I edited and forgot to change that on the querry. It is actually T, Z, XY and #* , i want to just remove. This data is in my TRX_REF column and i want to put it in a new column Invoive. I know i can start with a

SELECT TRX_REF AS Invoice,
FROM Table.1

But I am not sure how to proceed from here.
Go to Top of Page

jethrow
Starting Member

USA
37 Posts

Posted - 08/18/2013 :  09:02:22  Show Profile  Reply with Quote
First, I'd create a function:
Function InvoiceFormat(str)
    Static RegEx As Object
    If TypeName(RegEx) = "Nothing" Then
        Set RegEx = CreateObject("VBScript.RegExp")
    End If
    RegEx.Pattern = "T|Z|XY|\s*#\d*"
    InvoiceFormat = RegEx.Replace(str, "")
End Function


... then it's as simple as:
SELECT InvoiceFormat(TRX_REF) AS Invoice
FROM Table1


Microsoft SQL Server Noobie
Go to Top of Page

Controller1
Starting Member

8 Posts

Posted - 08/18/2013 :  10:59:35  Show Profile  Reply with Quote
Thank you, i am finding it hard to put the function in the SQL statement... Is there any way of doing like

REPLACE T with "" in SQL. Reason i am trying to as purely SQL as possible. If i stray into VBA then i will not learn the Power of SQL
Go to Top of Page

Controller1
Starting Member

8 Posts

Posted - 08/18/2013 :  11:05:30  Show Profile  Reply with Quote
I am seeing this link as offering possible solutioon to this in SQL but this does not seem to work in MS Access as it is done in sql server, how can i go round this?
http://www.sqlteam.com/article/using-replace-in-an-update-statement
Go to Top of Page

Controller1
Starting Member

8 Posts

Posted - 08/18/2013 :  13:28:26  Show Profile  Reply with Quote
I did the below and it has worked in the Query.

SELECT (Replace( TRX_REF ,'#4799', '')) As Invoice

However i still need help if anyone know how to do it for all numbers after # in MS Access SQL. i tried '#*' and '#%' but none worked.
Go to Top of Page

jethrow
Starting Member

USA
37 Posts

Posted - 08/18/2013 :  14:23:55  Show Profile  Reply with Quote
quote:
Reason i am trying to as purely SQL as possible. If i stray into VBA then i will not learn the Power of SQL ... I am seeing this link as offering possible solutioon to this in SQL but this does not seem to work in MS Access as it is done in sql server ...

What does purely in SQL mean? Purely in SQL ... what implementation of SQL? For instance, SQL Server has a procedural side - it's T-SQL. Oracle has a procedural aspect as well - it's PL/SQL. You can utilize RegEx by defining a function in T-SQL - but that wouldn't be purely SQL - as you have requested. I say all this to help you understand that VBA would be the procedural aspect to SQL in MS Access. If you think my example is "cutting corners" using VBA, you're mistaken.

That said, you would simply define the function in a Module. If you were to do this with a series of built-in string functions (Replace, InStr, Mid, etc), you would still be using VBA.

Microsoft SQL Server Noobie

Edited by - jethrow on 08/18/2013 17:16:19
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000