replace in MS Access sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

replace in MS Access sql

Author

Topic

Controller1
Starting Member

8 Posts

Posted - 2013-08-18 : 04:54:05

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

37 Posts

Posted - 2013-08-18 : 05:07:31

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

Controller1
Starting Member

8 Posts

Posted - 2013-08-18 : 07:39:26

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.

jethrow
Starting Member

37 Posts

Posted - 2013-08-18 : 09:02:22

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

Controller1
Starting Member

8 Posts

Posted - 2013-08-18 : 10:59:35

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

Controller1
Starting Member

8 Posts

Posted - 2013-08-18 : 11:05:30

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

Controller1
Starting Member

8 Posts

Posted - 2013-08-18 : 13:28:26

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.

jethrow
Starting Member

37 Posts

Posted - 2013-08-18 : 14:23:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources