Solved

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

Posted on 2014-11-28
14
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
14 Comments
 
LVL 50

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 50

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

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

Accepted Solution

by:
Huseyin KAHRAMAN 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 50

Expert Comment

by:Vitor Montalvão
ID: 40470485
@Delphiwizard: My observation for to consider only the last 11 characters is correct?
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Expert Comment

by:Huseyin KAHRAMAN
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 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