 |
|
|
 |
New Member
|
|
Aug 5, 2011, 04:49 AM
|
|
Extract part of string access?
Hi!
I have a large database, where I need to count different items how many times they have been run over the last few years. I've got pretty much everything figured out, except this:
The database is quite stupid, in one column there is a lot of info (timestamp, explanation, the thing I need, and somethimes something after that as well, with spaces between them (not allways the same amount either). I'm reading about exctrating part of string. But don't quite get the hang of it. I have 3 characters, both letters and numbers, that I need to separate from the column into a new one hopefully. The 3 characters change, but some are the same, I want to count them to see how many it is of each.
Hope any of this makes sense,
Thanks!
|
|
 |
Computer Expert and Renaissance Man
|
|
Aug 5, 2011, 09:27 AM
|
|
It is not good design to include more than one piece of information in a single field. For example, I generally use 5 fields for people names; Salutation (Mr, Ms, etc.), First, Middle, Last and Suffix (Jr, SR, etc.). It is a lot easier to piece separate fields together then break them apart.
So I would look to redesign this database.
Extracting a string from another string can be a bear unless there is a distinct pattern. You need to identify where the 3 characters fall within the string. If they always fall in the same place (i.e. the 21-23 characters) then you can use the MID function:
MID([fieldname],21,3)
If there is another character that identifies where the 3 characters you want start, then you can use the InStr function to find that character and use it in place of the 21 above:
MID([fieldname],INSTR(1,[fieldname],"char"),3)
|
|
 |
New Member
|
|
Aug 8, 2011, 12:03 AM
|
|
Hi Scott!
I know it's not a good design. I'm not the one who made it, but I have to work with it!
Thanks to you, I figured it out, and got what I wanted!
Thank you for your help! :D
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
To extract characters appearing right side of a particular string
[ 2 Answers ]
Mysql or excel function to extract characters after the occurrence of a particular string
For example: Suppose
"http://10.20.30.40:8080/aptv-web/PROGRAMS/TIMESNOW/LIVE/64BIT/TIMESNOWLive_03 "
This is the statement through which I need to extract the characters appearing right side of...
(Microsoft Excel) - Need to extract a specific number from text string
[ 2 Answers ]
For example, cell K1 looks like this
Z. Thornton(90), J. Bornstein(90), A. Jazic(65), C. Talley(90), Y. Cuesta(90), S. Kljestan(90), P. Nagamura(90), M. Lahoud(90), J. Braun(90), J. Padilla(46), Maicon Santos(60)
I want a formula that will give me the number to the right of a name if the cell...
SQL Expression to extract part of a string divided by dashes
[ 4 Answers ]
Hi,
I have a field (Tag_Num) in an Access database that contains a heap of values that look something like this;
12345-1234-ABC-40-CG1
12345-2868-ACC-40-CG1
12345-4897-ACC-40-CG1
12345-7874-BGC-40-CG1
12345-8756-BXC-40-CG1
12348-123-ABC-40-CG1
Cannot access part of my hard drive
[ 3 Answers ]
Hi everyone.
A month ago my computer got the blue scree of death. Anyway, I turned it off but it would turn on again. I tried to fix it, but ended up just making a mess. So, to the point, I pulled out my hard drive and connected it to an internal to external drive housing and plugged it in to...
View more questions
Search
|