Solved

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

Posted on 2014-11-28
14
70 Views
Last Modified: 2014-11-29
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
Comment
Question by:Delphiwizard
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40470353
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
 
LVL 32

Expert Comment

by:awking00
ID: 40470436
case when vat like '[a-Z][a-Z]%' then right(vat,len(vat) - 2)
     else vat
end
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40470440
@awking00:
I think you need to add the case that there's only a letter instead of 2.
0
 

Author Comment

by:Delphiwizard
ID: 40470472
Yes, there might be 1, 2 or 3 letters as country-prefix.
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 40470481
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
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40470485
@Delphiwizard: My observation for to consider only the last 11 characters is correct?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 51

Expert Comment

by:HainKurt
ID: 40470489
this also works...

 right('000' + vat ,11) vat_fixed
0
 

Author Comment

by:Delphiwizard
ID: 40470494
This works perfectly, thank you very much.
0
 

Author Comment

by:Delphiwizard
ID: 40470497
Sorry I gave the points by mistake to the incorrect answer.
I'll asked EE to reset.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40470503
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
 

Author Closing Comment

by:Delphiwizard
ID: 40471166
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 40471200
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
 

Author Comment

by:Delphiwizard
ID: 40471358
And that is a fact!
Thanks for the explanation.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
need help in sql 4 65
Nested cursor  in SQL 9 94
SQL Syntax join to include values from first table 3 27
SQL - SP needs a little help 9 20
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now