Replace Leading Zeros after the first 3 letters in a value with spaces

I don't know if this is the query:

DECLARE @Col0 VARCHAR(MAX)
SET @Col0 = '0000000005555'
SELECT SPACE(n)+RIGHT(@Col0,LEN(@Col0)-n) FROM (SELECT PATINDEX('%0[1-9]%',@Col0)n) AAA

Select allOpen in new window

Thank you again.

No this cannot work with the three introductory letters.
Use following formula to remove leading zeros from the 9 digits long number following three letters:

SELECT LEFT(ClaimNo, 3) + CAST(CAST(RIGHT(ClaimNo, 9) AS int) AS varchar(9))

Select allOpen in new window

Following query will replace leading zeros by spaces:

SELECT LEFT(ClaimNo, 3) + RIGHT(SPACE(9)+CAST(CAST(RIGHT(ClaimNo, 9) AS int) AS varchar(9)), 9)

Select allOpen in new window

select replace(claim_no, '0',' ') from table

I'm not sure what you mean by leading zeroes after the first three letters. If claim_no always starts with three letters, then the above should work.

Oops, I jut figured out what you meant, forget the above comment.

I think pcelba is on the right track, although I believe the second cast should be as char(9).

No, the char(9) does not work because it leaves trailing blanks on the output and successive RIGHT then provides incorrect result

@pcelba:

I have an error "Conversion failed when converting the nvarchar value '' to data type int."

Thank you again.

Are there always 12 characters in the ClaimNo?

What is the claim_no value which reports the conversion error?

The  PATINDEX('%0[1-9]%',@Col0) could also be used and it is very interesting approach but the CAST will do the same work if the claim_no format is XXXNNNNNNNNN.

@Norie:

Yes.

@pcelba:

It will create an Error if ClaimNo value is AAA0. How to work around on this?

Thank you.

What I mean is value is equal to AAA000000000.

If ClaimNo shorther than 12 characters is allowed then you would need one more CAST:

SELECT LEFT(ClaimNo, 3) + RIGHT(SPACE(9)+CAST(CAST(RIGHT(CAST(ClaimNo AS char(12)), 9) AS int) AS varchar(9)), 9)

Select allOpen in new window

A couple of questions for clarification:

• Are you wanting to replace the leading zeroes with spaces, or remove leading zeroes? In other words, should ABC00000123 become ABC123 or ABC      123?
• Are the nine characters after the three letters ALWAYS numeric?

I think the only way you'd get an error with pcelba's solution is if there were non-numeric characters in the 9 characters following the first three.

General query structure:
SELECT ClaimNo,
    CASE WHEN ClaimNoZeroCount = 0 THEN ClaimNo ELSE STUFF(ClaimNo, ClaimNoFirstDigit,
        ClaimNoZeroCount, REPLICATE(SPACE(1), ClaimNoZeroCount)) END AS ClaimNoModified    
FROM dbo.TableName
CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', ClaimNo) AS ClaimNoFirstDigit
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN SUBSTRING(ClaimNo, ClaimNoFirstDigit, 1) <> '0' THEN 0
        ELSE PATINDEX('%[^0]%', SUBSTRING(ClaimNo, ClaimNoFirstDigit + 1, 1000)) END AS ClaimNoZeroCount
) AS ca2


With sample data:
IF OBJECT_ID('tempdb.dbo.#TestData') IS NOT NULL
    DROP TABLE #TestData
CREATE TABLE #TestData ( ClaimNo varchar(100) NULL )
INSERT INTO #TestData VALUES('AAA002138961'),('BBB000003567'),('CCC000052093'),
('DDD040667709'),('EEE000000002'),('FFF509910020'),('JJJ000297327'),('MMM992231023'),
/* just to show the logic is able to handle any format */
('Unexpected00001Format'),('Unex00000Format'),('Un000000000234ABC')

SELECT ClaimNo,
    CASE WHEN ClaimNoZeroCount = 0 THEN ClaimNo ELSE STUFF(ClaimNo, ClaimNoFirstDigit,
        ClaimNoZeroCount, REPLICATE(SPACE(1), ClaimNoZeroCount)) END AS ClaimNoModified    
FROM #TestData
CROSS APPLY (
    SELECT PATINDEX('%[0-9]%', ClaimNo) AS ClaimNoFirstDigit
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN SUBSTRING(ClaimNo, ClaimNoFirstDigit, 1) <> '0' THEN 0
        ELSE PATINDEX('%[^0]%', SUBSTRING(ClaimNo, ClaimNoFirstDigit + 1, 1000)) END AS ClaimNoZeroCount
) AS ca2

I don't understand why you are posting new and new (incorrect) solutions to the question which has the correct solution already.

If you would read all posts then you could note the value AAA0 is possible as the ClaimNo. Of course, the question here is whether to convert it as "AAA         " or "AAA        0".

Eh ?

Sorry @pcelba, but I did read the posts which is why I continued on with my submission....

"AAA0" is not a legitimate requirement as far as I could ascertain. It was corrected. Maybe you didnt see : #a42655867 

What I mean is value is equal to AAA000000000.

Anyway.... As far as "AAA0" is concerned, my submission does cater for it and returns

claimno      new_claimno
------------ ------------
AAA0         AAA        0

(1 row affected)

Select allOpen in new window

After all, I read the requirement as 'leading zeros' so, a single zero should also work.

There would be a problem with (say) "AAA999" which should probably stay as it is, but in my submission it would right justify the number part. So, some additional checking is probably required. But then, we are told there will always be 12 characters. And as mentioned above, we can easily add in some checks.....

Other than that, I dont see why you would call it "(incorrect) solutions"

My apology Mark, your code works.

I don't understand why you are posting new and new (incorrect) solutions to the question which has the correct solution already.

Your code will fail -- completely crash -- if the remaining chars are not all numeric.  It will miss data if there happen to be more than 9 chars after the first three chars.  

OP may be willing to unconditionally guarantee the data will always match the current pattern at the risk of failure.  Then again, they may not.  I generally prefer code that's as flexible as possible to data changes/variations.

Hello Experts,

I really do apologize for a late response. Last Friday, I left work early. I did not have a chance to respond. My sincere apology.

@dbishop:

To answer your questions:
A couple of questions for clarification:
Are you wanting to replace the leading zeroes with spaces, or remove leading zeroes? In other words, should ABC00000123 become ABC123 or ABC      123?

--ABC      123

Are the nine characters after the three letters ALWAYS numeric?

--No sometimes there are 9 letters.

@Scott Pletcher:

I know your query is working. All I need is a simple query. Do I need to create cross apply?


@pcelba:

I still have an error if it is not numeric after the 3 letters. Your query is working also but I don't know how to work around the errors.

@MarkWillis:

Your query perfectly what I needed. It is simple and easy to understand. Mark, where should I put the these: TRY_CAST() if SQL2012, or check ISNUMERIC(), and LEN() > 3. I have SQL Management Studio 2014.

To all experts, please be patient with me, I am not an expert to SQL, I am still learning. I am still trying to learn the logic.

Thank you again for all your help.

Thank you again for sharing your intelligence and expertise to make this query a success. You are all indeed very helpful.

I really appreciate it from the bottom of my heart.

Hi Queenie L,

Well TRY_CAST() will either complete the CAST, or if it cannot, then gives NULL

Have a read of : https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-2017 it does save a lot of mucking about.

In this case, we would use it when casting to INT ie:

cast(substring(claimno,4,9) as int)

Select allOpen in new window

The challenge is, we dont know what is in that substring, so it could fail. Which is why we use try_cast().... But really, the statement is a bit bigger that that, because we are really looking at making it a varchar ie

cast(try_cast(substring(claimno,4,9) as int) as varchar)

Select allOpen in new window

Now, if try_cast() fails it returns NULL, and we dont want NULL, we want the original value. Easy enough, wrap it in an ISNULL() check ie

isnull(cast(try_cast(substring(claimno,4,9) as int) as varchar),substring(claimno,4,9))

Select allOpen in new window

Which uses the original (sub)string if the try_cast() returns NULL. Which means our new select statement looks like

select claimno, left(claimno,3) + right(space(9) + isnull(cast(try_cast(substring(claimno,4,9) as int) as varchar),substring(claimno,4,9)),9)
from #claims

Select allOpen in new window

Now, there is a little "gotcha" that you need to be careful of, and that is to make sure you end up with compatible datatypes for that column. Which is why we wait to check for the NULL result until AFTER we cast to varchar.

The only other problem is checking for LEN(). We should check for len(claimno) > 3  otherwise with the substring() starting at 4, it could fail. There are a couple of strategies that could be used.

One is : CASE WHEN LEN(CLAIMNO) > 3 then <code> end as new_claimno

The other is to simply extend the length of claimno in the substring .: substring(claimno+'    ',4,9)

Advantage of checking LEN() is it will account for NULL values

But then, there should probably be a WHERE claimno is not null.

What we strive to do is to 'accommodate' the unexpected errors so the code will work.
Does that explain it ?

@MarkWillis:

Yes.

Thank you again for your explanation. That helped a lot.