Delete left two characters of string if A-Z or a-z

Hi,

In my SQL Server database table VAT numbers are stored.
In the select-statement I need to remove max two left characters if they are within A-Z or a-z
NL12345678B01 -> 12345678B01  // First two characters removed
43234378B01 -> 43234378B01       // unchanged

Open in new window


How can this be done?
Stef MerlijnDeveloperAsked:
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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
It's look to me that you'll need always the last eleven characters, right?
If so, something like this will work:
SELECT RIGHT(vatNumber,11) FROM vatTable

Open in new window

0
awking00Commented:
case when vat like '[a-Z][a-Z]%' then right(vat,len(vat) - 2)
     else vat
end
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@awking00:
I think you need to add the case that there's only a letter instead of 2.
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.

Stef MerlijnDeveloperAuthor Commented:
Yes, there might be 1, 2 or 3 letters as country-prefix.
0
HainKurtSr. System AnalystCommented:
try this

with t as (
select 'NL12345678B01' as vat
union select '43234378B01'
union select 'A43234378B01'
union select 'DEF43234378B01'
)
select vat, stuff(vat, 1, patindex('%[0-9]%', vat)-1, '') vat_fixed from t

vat	vat_fixed
43234378B01	43234378B01
A43234378B01	43234378B01
DEF43234378B01	43234378B01
NL12345678B01	12345678B01

Open in new window

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Delphiwizard: My observation for to consider only the last 11 characters is correct?
0
HainKurtSr. System AnalystCommented:
this also works...

 right('000' + vat ,11) vat_fixed
0
Stef MerlijnDeveloperAuthor Commented:
This works perfectly, thank you very much.
0
Stef MerlijnDeveloperAuthor Commented:
Sorry I gave the points by mistake to the incorrect answer.
I'll asked EE to reset.
0
awking00Commented:
Are you saying there could be 1, 2, or 3 characters in the prefix (or 0 if not prefix), but they would always be followed by 11 characters without the prefix?
0
Stef MerlijnDeveloperAuthor Commented:
I see now why I gave the points to the incorrect answer. Previously the assign point link were placed below the answer and now above :-)
Thank you all for your help.

PS:
@Hainkurt: Your second solution doesn't work as my data doesn't have the same length. Sometimes the whole string consists only out of 8 characters, other times it can be as much as 14 characters.
0
HainKurtSr. System AnalystCommented:
actually i never understood this stuff & patindex thing :) just tried something and it worked... then I checked details to see whats happening here:

stuff(vat, 1, patindex('%[0-9]%', vat)-1, '')

patindex('%[0-9]%', vat) > gives you index of first numeric character
patindex('%[0-9]%', vat)-1 > gives you index of last non-numeric (from start), or index of char before first numeric character
stuff(vat, 1, patindex('%[0-9]%', vat)-1, '') > removes from 1st character until last non-numeric character, or replaces it with ''

result is what you want :)
0
Stef MerlijnDeveloperAuthor Commented:
And that is a fact!
Thanks for the explanation.
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.