How to generate 5 digit increment number based on Zone?

Question

 My Out put like This : If it is North "N00001
                                        N00002
                        If it is South "S00001
                                        S00002

Here is my Tried Code

  Declare @Zone varchar(20),@ZoneID int,@id varchar(10)

 Set @Zone = 'S'

 Select @ZoneID = cast(isnull(max(cast(replace(Idno,@Zone,'') as numeric))+1,'00000') as varchar) from memberprofiles Where left(idno,1) = @Zone
 Set @id = @Zone+cast(@ZoneID as varchar)   
 select @id

But Every Time I am Getting "S1" But I need "S00001"

How can i generate Zone wise Number generation

Answer 1

This question is a "closed as duplicate" candidate. But, as there are several flaws, I think it's worth an answer:

1. In your database you seem to have a column "Idno" with a leading character marking the zone. If this is true, you should - if ever possible - change the design. The number and the zone mark should reside in two columns. Any combination of them is a presentation issue

2. Your LEFT(Idno,1) will perform badly (read about "sargability"). If there's an index on Idno, you should be better off with Idno LIKE ' + @Zone + '%'

3. Are you sure, that in "memberprofiles" there's only one row where your WHERE clause is true? If not, which number would you expect in "@ZoneID" after your SELECT?

4. Your cast(isnull(max(cast(replace(Idno,@Zone,'') as numeric))+1,'00000') ... replaces the leading "S" with nothing, hoping there is a number left. You'll get the highest number (OK, this answers point 3, but is still very - uhm - hacky), still you expect a "NULL" where you'd return a "00000". This cries for a better design loudly :-)

5. You should try to get into "set based" thinking, rather then "procedural" thinking...

Try this

CREATE TABLE #memberprofile(Idno VARCHAR(100),OtherColumn VARCHAR(100));
INSERT INTO #memberprofile VALUES('N3','Test North 3'),('S24','Test South 24'),('N14','Test North 14')

DECLARE @Zone VARCHAR(20)='N';

SELECT *
      ,ZoneCode + REPLACE(STR(Number,5),' ','0') AS YourNewPaddedCode    
FROM #memberprofile
CROSS APPLY
(
    SELECT LEFT(Idno,1) AS ZoneCode
          ,CAST(SUBSTRING(Idno,2,1000) AS INT) AS Number 
) AS Idno_in_parts
WHERE ZoneCode=@Zone;
GO

--Clean up
--DROP TABLE #memberprofile

The result

Idno    OtherColumn  ZoneCode   Number  YourNewPaddedCode
N3      Test North 3    N        3      N00003
N14     Test North 14   N       14      N00014