De-concatenate ;-)

[w11z]

Hi everyone,
I am working on a access database and I have to separate a field that has been concatenated. For example the value of the field History is (for example) '10/23/2004Poster' (without the quotes) but it can also be 'View, 10/23/2004'. I want to separate the date from the word and put each in it's own field (rDate and rWord). There are 10 000 entries. How can I do this with SQL. I have been able to extract the word from the History field and place it in the rWord field (update myTable set rWord='Poster' WHERE History LIKE '*Poster*') - I have done this with 'View' and all other words found. But I haven't succeeded with the date. Can anyone help me? Thank

 

[dhookom]

Would the first number in this field always be the start of the date value? Are the dates entered consistently with 10 characters like "07/21/2005" or might I find "7/21/05"?

Is there always a date? Is the date always the last text in the field?

Are you willing to settle for a small user-defined function that extracts the date value?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[w11z]

Thank Duane for your reply,

It is presented in various style : '10/27/2003Poster,', '3/4/2004Viewbook,', '2004-04-28Viewbook', 'Viewbook, 07-09-2004', ' Viewbook, 25/02/2005' and '2/3/2004Poster'. I haven't been able to do anything with this.

 

[fredericofonseca]

Are the keywords you are looking for always the same or can you have totally different values on your field?

If they are always the same, you can create a VBA function that will search the supplied string for particular values, and split the string according to the position of the strings.

for example, if you can assume that (example)
If search string is on first position
then there will be a "," and date will follow
else
date will be before string
end if

then it is kind of easy to build such function, in a way that the date is always returned on the first 10 chars, with a fixed format (e.g. YYYY-MM-DD), and the search string always from position 11 onwards.

you could then use the following sql idea.

select mid(my_split_function(my_tbl_field),1,10) as my_datefield from my_table




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dhookom]

Or, if you have fields
[tt]
rWord History
Poster Poster2/1/2005
View 8/22/05View
[/tt]
You could use an expression like

TheDate:CDate(Trim(Replace([History],[rWord],"")))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[w11z]

Thank you both, I have tried Duane suggestion: SELECT (Trim(Replace([History],[rWord],""))) from tableName;

It not perfect but at least it's more usable that way. Tha results are something like :

'10/6/2003 ,' or ', 03/11/2004'

I will work on taking out the comma

Frederico : The values are not always identical. There are numerous ways the values are presented. Also, I don't really understand the ' my_split_function')

Thank you

 

[fredericofonseca]

the issue there is not if they are different.

If you only have 20 or 30 values (or even 100) it is fine as long as YOU KNOW WHAT THEY ARE.

If they can be unknown then you can use a function.


the my_split_function would be a user defined function you would create that would be called using a parameter, e.g. you "raw" field, and would return a string made up of "date" + whatever string was on the raw field (apart from the date).

Duane solution will not work as you will have several options. on the string to search, and you can not do several strings with just one replace.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[dhookom]

You can nest replace functions inside each other.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredericofonseca]

try and do that with 30 strings


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RoyVidar]

Just getting the date?

Here's a go at Regular Expressions. This is probably not the fastest method around, and I can't be sure I've got the pattern right, but see how it goes (btw it will barf if the field is Null - but alter to variant in the declaration, and add a test for it, should that be a possibility)...

[tt]' declaration section of a standard module
private mre as object

' function
Public Function Extracting(ByVal v_strIn As String) As String
Dim mc As Object
If (mre Is Nothing) Then
' instantiate Regular Expression object
Set mre = CreateObject("vbscript.regexp")
mre.Pattern = "(\d{1,2}(-|/)\d{1,2}(-|/)(\d{4}|\d{2}))"
End If
Set mc = mre.Execute(v_strIn)
If mc.Count > 0 Then
Extracting = mc(0).Value
End If
End Function[/tt]

Roy-Vidar

 

[raskew]

Assuming that the date portion of the string is either at the
very beginning or very ending of the string, the following
seems to correctly (tested against every example in the
preceding posts) extract the date from the string, regardless
of delimiters (e.g, ",", "", etc.). It'll still take a little
work to return the non-date portion.

Function UnScramble(pstr As String) As Date
'********************************************
'Purpose:   Extract an embedded date from a
'           string that either begins or ends
'           with a date (in string format).
'Coded by:  raskew
'Inputs:    1) ? UnScramble("Poster2/3/04")
'           2) ? UnScramble("8/22/05,View")
'Output:    1) 2/3/04
'           2) 8/22/05
'********************************************

Dim ynLeft  As Boolean
Dim n       As Integer
Dim strHold As String
Dim strKeep As String

    strKeep = Trim(pstr)
    'is the date at the front or rear of the string?
    ynLeft = IsNumeric(Left(strKeep, 1))
    'max length of date, e.g. '12/31/2004'
    n = 10
    strHold = IIf(ynLeft, Left(strKeep, n), Right(strKeep, n))
    If ynLeft Then
      Do While Not IsNumeric(Mid(strHold, n, 1))
         n = n - 1
      Loop
      UnScramble = CDate(Left(strKeep, n))
    Else
      n = Len(strKeep) - n
      Do While Not IsNumeric(Mid(strKeep, n, 1))
         n = n + 1
      Loop
      UnScramble = CDate(Mid(strKeep, n))
    End If

End Function

HTH - Bob

HLDYSIWTFMRTCBWAF?

 

[w11z]

Back to work this morning,

I've looked at your post and want to thank you. I've succeeded in getting the word (Poster, Viewbook, etc.) on to a separate field. The remaining (date, comma, and other unwanted 'junk') is in another field (temp). I will have to use different ways suggested here to extract just the date (which comes in different pattern : ', 09/16/2003 ,' or '9/8/2003,' or '1/22/2004Ohio , 22 jan 04' or '1/22/2004, 22 Jan 04' or '18/01/2005, 18.01.2005'

Anyway, you get the idea.

There is one question from reading your posts. I'm working in Access SQL View. How do I work with a function in SQL View. Do I have to use a form and a button taht will trigger the function?

Thank again

 

[fredericofonseca]

a UDF, or user defined function, is created as if it was a code module, and is used within your SQL code, as per the example I gave you, or indeed as you would use any other function like trim, convert, switch, NZ and so on.

A UDF can return only a determined type of variables, but on your particular case you need one to return a string, and eventually another to return a date.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[RoyVidar]

Is it still only the date you're after? The other text is OK? When you get the function thingie working, for mine try the pattern below in stead of the original, this is tweaked a little according to the last provided date formats (or are there still other formats...). Note - there is no validation of the dates with this method, that you would need to to somewhere else - or better, disallow entering dates like this.

[tt]"\d{1,2}((-|/|\.)\d{1,2}(-|/|\.)| [a-zA-Z]{3} )(\d{4}|\d{2})"[/tt]

Roy-Vidar

 

[w11z]

Many thanks!

I have finished what I wanted to do. Your suggestions were very helpful. There were to many different patterns in the Date field (for example : 03-01-2004, 01-03-2004, 03.01.2004, 01.03.2004, 03/01/2004, 01/03/2004, 2004-03-01, 2004-01-03, etc.)

I unfortunately had to go through every records to standardize everything. Took me all morning but now it's done. Again thank you very much