Trying to use a mid function in an Access query to pull data from a long sting.

Question

Post reply

Trying to use a mid function in an Access query to pull data from a long sting.

Kyle Washington

SSC Veteran

Points: 218
More actions
July 21, 2005 at 8:09 pm

#66468

I appologize if this a newb question. I've spent hours trying to find a solution.
As the Title suggests, I'm tryng to use a mid() function in an Access query. I am linking to a table in a SQL 2000 database. The fields looks like this:
Table: Events Column: EXT_TAGS
@1@ANONYMOUS LOGON~1~@2@NT AUTHORITY~2~@3@(0x0,0x12D670)~3~@4@3~4~
@1@PURPLE$~1~@2@TEST~2~@3@(0x0,0x12E2C8)~3~@4@3~4~
@1@PURPLE$~1~@2@TEST~2~@3@(0x0,0x12E871)~3~@4@3~4~
@1@ANONYMOUS LOGON~1~@2@NT AUTHORITY~2~@3@(0x0,0x12EF80)~3~@4@3~4~
@1@wshngtn~1~@2@TEST~2~@3@(0x0,0x12E13C)~3~@4@3~4~
@1@wshngtn~1~@2@TEST~2~@3@(0x0,0x148F33)~3~@4@3~4~
@1@PURPLE$~1~@2@TEST~2~@3@(0x0,0x148EF3)~3~@4@3~4~
@1@PURPLE$~1~@2@TEST~2~@3@(0x0,0x149C6D)~3~@4@3~4~
@1@PURPLE$~1~@2@TEST~2~@3@(0x0,0x14B8E7)~3~@4@3~4~
As you can see, it is separating the various fields with @#@ and ~#~. the # = the field number. I am desparatly trying to figure out how to extract the data from this table using the @#@ and ~#~ as markers. I have successfully done this when the table was in the local database. I imported it into SQL Server 2000 because the database was getting HUGE, over a GB and it was taking forever to run a simple query.
Here is an example of the statement that worked when the table was still in access format:
Object Server: Mid([EXT_TAGS],(InStr(1,[EXT_TAGS],"@1@",1)+3),((InStr(1,[EXT_TAGS],"~1~",1))-(InStr(1,[EXT_TAGS],"@1@",1)+3)))
I'm using the mid function to identify the starting and end points, subtracting the start from the end to get the length and compansating for the @#@ & ~#~ field oveners and closers.
When I try to run the query I get an syntax error (comma)in query expression...
Any idea what I can do?
I've even tried simple right $ left functions, I get the same darn results.
Thank you so much for any help you can offer!!!
Kyle

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Pham Tuan Phat SSC-Addicted Points: 459 More actions · Answer 1

In T-SQL syntax, there is no existing the MID function, but the SUBSTRING function. See help for more information.

Have a gook luck.

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Is it not possible for you to parse the string and store each value in a separate field ?!

**ASCII stupid question, get a stupid ANSI !!!**

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 3

CHARINDEX() AND PATINDEX() are the SQL functions that you need to identify substring locations but this query is going to be slow. I would support strongly Sushila's suggestion to parse the records into separate columns and add some indexes.

scking SSCrazy Points: 2598 More actions · Answer 4

Is this a one time action or would you need to do it many times? Personally, I would use REPLACE on the text string to create a more standard formant file, like comma delimited, load it to a temp table and look at the table using SELECT before taking any actions.

Tony John-224503 SSC Enthusiast Points: 112 More actions · Answer 5

SELECT

Mid(ext_tags, InStr$(1,[ext_tags],"@1@",1)+3, InStr$(1,[ext_tags],"~1~",1)-4) AS Field1

, Mid([ext_tags], InStr$(1,[ext_tags],"@2@",1)+3, InStr$(1,[ext_tags],"~2~",1)-InStr$(1,[ext_tags],"@2@",1)-3) AS Field2

, Mid([ext_tags], InStr$(1,[ext_tags],"@3@",1)+3, InStr$(1,[ext_tags],"~3~",1)-InStr$(1,[ext_tags],"@3@",1)-3) AS Field3

, Mid([ext_tags], InStr$(1,[ext_tags],"@4@",1)+3, InStr$(1,[ext_tags],"~4~",1)-InStr$(1,[ext_tags],"@4@",1)-3) AS Field4

FROM <tablename>;

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

I'm with scking....except that I would import the csv file into a "regular" table and then select from it...

just a note...there are places when these markers occur 2 at a time - so have to take care of those fields....eg:

@1@ANONYMOUS LOGON~1 ~@ 2@NT AUTHORITY~2 ~@ 3@(0x0,0x12D670)~3 ~@ 4@3~4~

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 7

SELECT

Substring([ext_tags], Charindex('@1@',[ext_tags],1)+3, Charindex('~1~',[ext_tags],1) - Charindex('@1@',[ext_tags],1)+3) AS Field1

, Substring([ext_tags], Charindex('@2@',[ext_tags],1)+3, Charindex('~2~',[ext_tags],1) - Charindex('@2@',[ext_tags],1)+3) AS Field2

, Substring([ext_tags], Charindex('@3@',[ext_tags],1)+3, Charindex('~3~',[ext_tags],1) - Charindex('@3@',[ext_tags],1)+3) AS Field3

, Substring([ext_tags], Charindex('@4@',[ext_tags],1)+3, Charindex('~4~',[ext_tags],1) - Charindex('@4@',[ext_tags],1)+3) AS Field4

FROM <tablename>

* Noel

Tony John-224503 SSC Enthusiast Points: 112 More actions · Answer 8

Noel,

I wrote the query in T-SQL first and converted it back for use in MS-Access because that's what Kyle needs it in. You just converted it back to what I had first started off with in the first place.

Tony.

Tony John-224503 SSC Enthusiast Points: 112 More actions · Answer 9

By the way Noel, your query does not return the expected results.

It returns the below:

Field1 Field2 Field3 Field4

---------------------------------------------------------------------

ANONYMOUS LOGON~1~@2@ NT AUTHORITY~2~@3@ (0x0,0x12D670)~3~@4@ 3~4~

<A href="mailtoURPLE$~1~@2">PURPLE$~1~@2@ TEST~2~@3@ (0x0,0x12E2C8)~3~@4@ 3~4~

<A href="mailtoURPLE$~1~@2">PURPLE$~1~@2@ TEST~2~@3@ (0x0,0x12E871)~3~@4@ 3~4~

ANONYMOUS LOGON~1~@2@ NT AUTHORITY~2~@3@ (0x0,0x12EF80)~3~@4@ 3~4~

wshngtn~1~@2@ TEST~2~@3@ (0x0,0x12E13C)~3~@4@ 3~4~

wshngtn~1~@2@ TEST~2~@3@ (0x0,0x148F33)~3~@4@ 3~4~

<A href="mailtoURPLE$~1~@2">PURPLE$~1~@2@ TEST~2~@3@ (0x0,0x148EF3)~3~@4@ 3~4~

<A href="mailtoURPLE$~1~@2">PURPLE$~1~@2@ TEST~2~@3@ (0x0,0x149C6D)~3~@4@ 3~4~

<A href="mailtoURPLE$~1~@2">PURPLE$~1~@2@ TEST~2~@3@ (0x0,0x14B8E7)~3~@4@ 3~4~

The correct T-SQL query is:

SELECT

SUBSTRING([ext_tags], CHARINDEX('@1@',[ext_tags])+3, CHARINDEX('~1~',[ext_tags])-4) AS Field1

, SUBSTRING([ext_tags], CHARINDEX('@2@',[ext_tags])+3, CHARINDEX('~2~',[ext_tags])-CHARINDEX('@2@',[ext_tags])-3) AS Field2

, SUBSTRING([ext_tags], CHARINDEX('@3@',[ext_tags])+3, CHARINDEX('~3~',[ext_tags])-CHARINDEX('@3@',[ext_tags])-3) AS Field3

, SUBSTRING([ext_tags], CHARINDEX('@4@',[ext_tags])+3, CHARINDEX('~4~',[ext_tags])-CHARINDEX('@4@',[ext_tags])-3) AS Field4

FROM <tablename>;

which returns the expected results:

Field1 Field2 Field3 Field4

--------------------------------------------------

ANONYMOUS LOGON NT AUTHORITY (0x0,0x12D670) 3

PURPLE$ TEST (0x0,0x12E2C8) 3

PURPLE$ TEST (0x0,0x12E871) 3

ANONYMOUS LOGON NT AUTHORITY (0x0,0x12EF80) 3

wshngtn TEST (0x0,0x12E13C) 3

wshngtn TEST (0x0,0x148F33) 3

PURPLE$ TEST (0x0,0x148EF3) 3

PURPLE$ TEST (0x0,0x149C6D) 3

PURPLE$ TEST (0x0,0x14B8E7) 3

Best regards,

Tony.

Kyle Washington SSC Veteran Points: 218 More actions · Answer 10

First off, you people are really nice! I was expecting to get flamed and called a newb and other stuff. Thanks for spending so much time and effort on it!

I thought that I was parsing the fields. I guess I don't understand how you might go about doing it. I have secondary queries that are pulling from this initial query, adding some filters to some of them, and dumping the parsed values into another table that contains only information we remotly care about - stuff that we really need to be keeping due to Federal Regulations. I then have a third level of queries run that add extra filters and puts them into yet another table. This may seem over kill, but I don't have the expertise to build all this into a single sql statement. It's the easiest thing for us, and it works for us.

I tried using the suggestions, but I kept getting an undefined function 'mid' in expression, same thing with trying the substring function.

I don't have any control over how the data comes to us, so if you were reffering to parsing the data instead of putting it all in a long string, we can't.

Again, Thank you so much.

sushila SSC-Dedicated Points: 35293 More actions · Answer 11

Kyle - I'm not at all sure I understand your ultimate goal...but if you select some fields based on certain criteria and store them in one table and then select remaining fields based on some other criteria and store them in a third table (which is what I understood) - then why don't you just post the filters that you are applying to each of these fields with an explanation and maybe someone can come up with a solution where appropriate filters are applied to the appropriate fields and the string can be parsed accordingly...as opposed to parsing it and then applying filter 1 and filter 2...

lastly...all this may be moot because you may have it working with Tony's or Noel's code but it seems that you still get an error message - ???????????

**ASCII stupid question, get a stupid ANSI !!!**

Kyle Washington SSC Veteran Points: 218 More actions · Answer 12

Ok, here is another attempt at explaining what I'm looking for.

Please forgive if I'm repeating myself..

We have a program that is pulling in data from our security logs on our windows servers. This program puts the data into a SQL database that will allow us to search for events in there. We are doing all of this work because we are not satisfied with the builtin reporter program in the log gathering program. The prgram deposits the data into a table in the SQL 2000 database. It has multiple columns, but there is one column that contains the real meat of the infor we are looking for. It puts the data into a string that looks like this: @1@dataA~1~@2@dataB~2~@3@dataC@3@...

We want to be able to parse the information out of that string so that we can put them into another table so they look like this:

column1 column2 column3

dataA dataB dataC

I do not know enough about SQL to write anything outside of using Access, I'm using 2002, so I'm looking for something to be able to pull out that data from the string. At this point, I would not worry about applying filters. If I can get the parsing to work, then I feel very confident that I can get the filters to work.

I tried using the above suggestions, but I get an undefined function error.

Tony John-224503 SSC Enthusiast Points: 112 More actions · Answer 13

Hi Kyle,

Let me see if you understand me clearly and then you can help me understand you clearly.

The below mentioned SELECT statement using only those Access functions that have been there with Access all the way to the most latest version in other words, they are compatible in any version. So try this statement once again for me please and let me know if it does not work. (Please note, you need to replace the <tablename> with the real table name that Access has linked to generally dbo_... or username_...):

SELECT

Mid$(ext_tags, InStr$(1,[ext_tags],"@1@",1)+3, InStr$(1,[ext_tags],"~1~",1)-4) AS Field1

, Mid$([ext_tags], InStr$(1,[ext_tags],"@2@",1)+3, InStr$(1,[ext_tags],"~2~",1)-InStr$(1,[ext_tags],"@2@",1)-3) AS Field2

, Mid$([ext_tags], InStr$(1,[ext_tags],"@3@",1)+3, InStr$(1,[ext_tags],"~3~",1)-InStr$(1,[ext_tags],"@3@",1)-3) AS Field3

, Mid$([ext_tags], InStr$(1,[ext_tags],"@4@",1)+3, InStr$(1,[ext_tags],"~4~",1)-InStr$(1,[ext_tags],"@4@",1)-3) AS Field4

FROM <tablename>;

If you still get an undefined function error it is not coming from this SELECT statement and is coming from somewhere else. I have tested this statement on my machine and it works great!

Hope this helps you.

Tony John.

Stewart Joslyn SSCertifiable Points: 6131 More actions · Answer 14

I think there is some confusion going on here between Access and SQL Server.

The Access functions are MID() and INSTR().

The corresponding SQL functions are SUBSTRING() and CHARINDEX().

The same algorithm will work on either platform; you just have to modify the syntax appropriately. I get the impression that you have been trying to use Access functions in SQL and vice versa.