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
mambo2015Asked:
Who is Participating?
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:

Select
    UCase(Left([Colour], 3)) As ColourCode,
    Pitch
From
    Pitches

Or, if you need one field only:

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

/gustav
0

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 DBACommented:
Hi,

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

HTH
  David
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Thanks.
0
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.

/gustav
0
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...


JeffCoachman
0
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'.

Thanks.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.