Ok, here we go... Assuming that you have the company name in a variable and the fact that you're using a version of T-SQL that cannot use functions, here's how I'd do it...
--===== Company name is in a variable
DECLARE @CompanyName VARCHAR(256)
SELECT @CompanyName = 'A & B Cleaners'
PRINT @CompanyName --Just for verification... you can remove this line
--===== Using a "Tally" table as a loop driver, remove all characters that
-- are NOT in the in range of A to Z (upper or lower case)
SELECT @CompanyName = STUFF(@CompanyName,PATINDEX('%[^A-Z]%',@CompanyName),1,'')
FROM dbo.Tally t
WHERE t.N <= LEN(@CompanyName)
AND SUBSTRING(@CompanyName,t.N,1) LIKE '[^A-Z]'
--===== Grab just the left six characters of what remains.
SELECT @CompanyName = LEFT(@CompanyName,6)
--===== Display the result (just for verification... you can remove this line)
If you don't have a Tally table, please see the article at the following URL for how to build one, what it is, and how it works. It's a very useful tool that can frequently be used to replace loops.
If you need to do this to a whole table column, please post back.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)