Generate a string format based on fixed length and numeric units on increment

  • I had to write a SP to generate a string with the combination of Country(7 chars)+Province(1 char) + numeric number starts from 01. for eg: JAMAICAK01 where JAMAICA(Country),K(Province) and 01 is the numeric number that gets incremented by 1 for each transaction. The issue I have here is the generated string length max is 10,can be lesser than 10 but not >10.It should be handled in a way with certain rules like 1. the combination don't exist 2. When the numeric unit changes from tens to hundreds making the string length >10, I need to remove the right chars for eg JAMAICAKKK10 to JAMAICAK10 from the right to make the total max length 10.

    Declare @Province char(2)

    Declare @Country varchar(10)

    declare @CounProv varchar(10)

    Declare @SimilarCounPRov varchar(max) = '';

    declare @FinalString nvarchar(12)

    declare @s-2 varchar(50)

    declare @s1 varchar(50)

    declare @s2 varchar(50)

    Set @Province = LEFT('KINGSTON', 1)

    Set @Country = LEFT('JAMAICA', 7)

    Set @CounProv = @Country+@Province

    Select @SimilarCounPRov = MAX(field1) from dbo.table where field1

    LIKE '%JAMAICAK%'

    if @SimilarCounPRov IS NOT NULL

    BEGIN

    Set @s-2 = (select fn_AlphaOnly('%JAMAICAK99%')) -- returns JAMAICAK

    Set @s1 = (select fn_NumericOnly('%JAMAICAK99%')) -- returns 199

    set @s2= @s1 +1 -- increment by 1

    if len(@FinalString) > 10

    ----

    need help here----`

  • Suppose the code you need to calculate is called the "Country Province Number" CPN.  The issue here is the example you provided only declares variables for a single record and the sequence number is parsed from another CPN.

    Does your procedure needs to operate on a single record where the "previous CPN" is passed in as a variable?  Or does your procedure need to operate on a table with many rows?

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thanks for your response. My procedure here need to operate on a table with possibility of many rows having the same ProvinceCountry combination with numeric sequence starting from 01 as below. The new CPN code should have the next sequence number with same ProvinceCountry combination with the rule of having total length of 10, removing chars from right on ProvinceCountry Combination depending on numeric unit.

    Eg:

    JAMAICAK01

    JAMAICAK02

    JAMAICAK03

    JAMAICAK04

    JAMAICAK05, ..........

    JAMAICAK99

    JAMAICA100 (here letter K is truncated to accommodate length 10 on sequence numeric value)

  • Why do you declare province to be char(2) and then assign it left(col_name, 1) which is only 1 character?

    Does the sequence reset for each country?

    What is the needed sort order of the rows?

    Could you provide the DDL for this table?

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • You can create a tracking table and use a Quirky Update in a proc to do this

    CREATE TABLE dbo.CountryProvNumber (
    Country varchar(10) NOT NULL
    , Province varchar(2) NOT NULL
    , LastUsedNum int NOT NULL DEFAULT(0)
    , PRIMARY KEY CLUSTERED (Country, Province)
    );
    GO
    CREATE PROCEDURE dbo.pr_GetNextCountryProvCode
    @Country varchar(10)
    , @Province varchar(2)
    , @CountryProvCode varchar(10) OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @NextNum int;
    DECLARE @TextNum varchar(10);

    INSERT INTO dbo.CountryProvNumber (Country, Province)
    SELECT @Country, @Province
    WHERE NOT EXISTS (SELECT 1 FROM dbo.CountryProvNumber AS cpn WITH (XLOCK, HOLDLOCK)
    WHERE cpn.Country = @Country
    AND cpn.Province = @Province);

    UPDATE dbo.CountryProvNumber
    SET @NextNum = LastUsedNum = LastUsedNum +1
    WHERE Country = @Country
    AND Province = @Province;

    SET @TextNum = CASE WHEN @NextNum < 10 THEN '0' ELSE '' END + CONVERT(varchar(10), @NextNum);
    SELECT @CountryProvCode = LEFT(@Country + @Province, 10 -LEN(@TextNum)) + @TextNum;

    END;
    GO
    DECLARE @CountryProvCode varchar(10);

    EXEC dbo.pr_GetNextCountryProvCode
    @Country = 'JAMAICA'
    , @Province = 'K'
    , @CountryProvCode = @CountryProvCode OUTPUT;

    SELECT CountryProvCode = @CountryProvCode;
  • Sory @province is not 2 chars it's 1 char. That's the business rule i was asked to do so. 1 char from province 7 chars from Country with seq no. for each record that contains the same combination or different provincecountry that starts with seqno. 01.

  • bplvid wrote:

    Sory @province is not 2 chars it's 1 char. That's the business rule i was asked to do so. 1 char from province 7 chars from Country with seq no. for each record that contains the same combination or different provincecountry that starts with seqno. 01.

     

    So change the declaration of Province in the table and @Province in the proc from varchar(2) to char(1)

  • Step 1: generate some dummy data

    /* create a table to hold the country and province values  */
    /* as well as a column to store the cntry_prov_code */
    drop table if exists dbo.country_province;
    go
    create table dbo.country_province(
    cp_pkint identity(1,1) constraint pk_country_provide primary key not null,
    countryvarchar(10) not null,
    provincechar(1) not null,
    cntry_prov_codevarchar(10) default 'XXXXXXXY##');
    go

    /* country dummy data (4 values * 101 rows = 404 rows)*/
    drop table if exists #cntry;
    go
    create table #cntry(countryvarchar(10) not null);
    insert #cntry(country) select top 101 'Albania' from sysobjects;
    insert #cntry(country) select top 101 'BahamasIsl' from sysobjects;
    insert #cntry(country) select top 101 'Chile' from sysobjects;
    insert #cntry(country) select top 101 'Denmark' from sysobjects;
    go

    /* province dummy data (3 unique values = 3 rows) */
    drop table if exists #prov;
    go
    create table #prov(provincechar(1) unique not null);
    insert #prov(province)
    values ('A'), ('B'), ('C');

    /* table to test with: (404 rows * 3 rows = 1,212 rows total) */
    insert dbo.country_province(country, province)
    select
    c.*,
    p.*
    from
    #cntry c
    cross join
    #prov p;

    Step 2: Run update cte and select all records:

    with cp_cte(country, province, cntry_prov_code, cntry_prov, row_sequence) as (
    select
    country,
    province,
    cntry_prov_code,
    concat(left(country, 7), province) cntry_prov,
    row_number() over(partition by concat(left(country, 7), province) order by concat(left(country, 7), province) asc) row_sequence
    from
    dbo.country_province)
    update cp_cte
    set
    cntry_prov_code = concat(left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))), concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))

    select * from dbo.country_province;

    Step 3: To see the parts of how it comes together (instead of the update, run this select)

    with cp_cte(country, provice, cntry_prov, row_sequence) as (
    select
    country,
    province,
    concat(left(country, 7), province) cntry_prov,
    row_number() over(partition by concat(left(country, 7), province) order by concat(left(country, 7), province) asc) row_sequence
    from
    dbo.country_province)
    select
    cntry_prov,
    left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))) stem,
    concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))) sequence_str,
    concat(left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))), concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9)))) cntry_prov_code,
    len(concat(left(cntry_prov, 10-len(concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))), concat(case when len(cast(row_sequence as varchar(9)))=1 then '0' end, cast(row_sequence as varchar(9))))) code_length,
    row_sequence
    from
    cp_cte c;

    Pretty sure this demonstrates all of the business rules required.

     

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • This was removed by the editor as SPAM

  • Instead of inventing your own encoding scheme, did you ever consider using the ISO Standards that are already in place? Just Google them. This is as silly as using the Potrzebie System of Weights and Measures (https://en.wikipedia.org › wiki › Potrzebie).

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply