How to truncate sql string data

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.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
That could be:

    UCase(Left([Colour], 3)) As ColourCode,

Or, if you need one field only:

    UCase(Left([Colour], 3)) & ", " & Pitch As ColourPitch


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:

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

Jeffrey CoachmanMIS LiasonCommented:
Then you will have to decide on the spelling
Grey or Gray

Or modify Gustav's post to use the first 4 characters
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

mambo2015Author Commented:
Thanks, Gustov!
mambo2015Author Commented:
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?

Gustav BrockCIOCommented:
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.

Jeffrey CoachmanMIS LiasonCommented:
...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...

mambo2015Author Commented:
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'.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.