asked on

How to truncate sql string data

Hi,
I would like to retrieve text from my access (mssql) database, but I would like the result to be truncated based on a set rule. For example:

Select Colour, Pitch from Pitches

Open in new window

will give me the colour and name of the pitch,

but, if say the result was "Green, Centre Pitch", I would like the results to be as follows: "GRE, Centre Pitch"

Obviously, if the colour was a three letter word then the truncation should not happen.

Thanks

Thanks

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Todd

Hi,

Question: Will all the colours truncate nicely to unique 3 characters? What if Grey was a pitch colour?

HTH
David

Jeffrey Coachman

Then you will have to decide on the spelling
Grey or Gray
http://blog.dictionary.com/gray-or-grey/
;-)

Or modify Gustav's post to use the first 4 characters

mambo2015

ASKER

Thanks, Gustov!

mambo2015

ASKER

Very good point, Jeffrey.

Actually, I am now facing that problem...

We are using two names for the shades of colour, eg, Lawn Green, Dark Grey, etc. So now Lawn Green should be LGR, and Dark Grey should be DGR!

For now we will not mind Dark Green and Dark Grey having similar abbreviations (we don't have them!), but I am now having trouble to identify the space in between and combine them as LGR, etc...

Any go around that I can use?

Thanks.

Gustav Brock

The only true solution is to have a table the two fields - ColourName and ColourCode or similar - and then look up these when you have one field and need the other.

/gustav

Jeffrey Coachman

...or just use a numeric field for this...
As you can see, ...there will always be confusion over the color names....
If there will always be ambiguity, ...then what are these abbreviations used for?

Just like not referencing a "Name" field for a person (because many people can have the same name), ...instead you just reference the PersonID (a unique numeric value for each person)

So in your case, ...display the "Full Color Name" (so there is never any confusion), ...then reference the "Color Code", ...behind the scenes
The benefit here that it is easier to reference a numeric value in your code than a text string.
For example, this is a lookup of the color "Text":
Dlookup("ColorName", "tblColors", "ColorID=" & "'" & me.txtColorAbbrev & "'")

Here is a lookup for the color "Numeric Value":
Dlookup("ColorName", "tblColors", "ColorID=" & me.txtColorID)

Just FYI...

JeffCoachman

mambo2015

ASKER

Thanks, Jeff, Gustav

I get the point. But I wanted to finish up what I had started in case I need it in future. So this is what I got:

Abbr: UCase(Left([Colour],1)) & UCase(Mid([Colour],InStr(1,[Colour]," ")+1,2))

Open in new window

Which gives me DGR for 'Dark Green'.

Thanks.