Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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?
0
Stef Merlijn
Asked:
Stef Merlijn
  • 5
  • 3
  • 3
  • +1
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now