CODEWITHSUNDEEP
sql-serverstringcompare
Arthur Rey
Arthur Rey

Reputation: 3058

Search for a common prefix between two strings

I have a Stored Procedure:

ALTER PROCEDURE [dbo].[traxs_Paybook_Data_Validate]

    @session_id varchar(30)
    @paybook_start_number varchar(30)
    @paybook_end_number varchar(30)

AS

    UPDATE traxs_temp..__PaybookImport SET
        /* BEGIN CHANGE */
        prefix = null,
        start_number = CAST(@paybook_start_number AS int),
        end_number = CAST(@paybook_end_number AS int)
        /* END CHANGE */
    WHERE   
        session_id = @session_id

Values were like:

@paybook_start_number = 100

@paybook_end_number = 200

Now paybook numbers can have a prefix, i.e:

@paybook_start_number = ABC100

@paybook_end_number = ABC200

Prefix is not always the same, neither is its length. I need to find the prefix if one, store it into prefix and remove it from paybook numbers before casting them.

Thanks

Upvotes: 0

Views: 608

Answers (2)

user3239820
user3239820

Reputation:

Try this:

DECLARE @z VARCHAR(32) = 'ukasd10';
SELECT LEFT(@z, PATINDEX('%[0-9]%', @z) - 1) AS Prefix,REPLACE(SUBSTRING(@z, PATINDEX('%[0-9]%', @z), LEN(@z)), ',', '') AS Digits

and likewise use this logic to update the column Prefix.... Thanks

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

You need a Function to extract Number/Numeric value from your input string and a function to extract Alphabets from the Input string.

Function To Extract Numbers

CREATE FUNCTION dbo.fn_Extract_Numbers
(
  @string NVARCHAR(100)
)
RETURNS INT 
AS
BEGIN 
    DECLARE @int_Value INT;


SELECT @int_Value = LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(@string, pos, LEN(@string))
    FROM (
           SELECT @string AS string , PATINDEX('%[0-9]%', @string) AS Pos
         ) d
     ) t

  RETURN @int_Value;
END

Function To Extract Alphabets

CREATE FUNCTION dbo.fn_Extract_Alphabets
(
  @string NVARCHAR(100)
)
RETURNS NVARCHAR(100) 
AS
BEGIN 
    DECLARE @Alpha_Value NVARCHAR(100);


SELECT @Alpha_Value = LEFT(subsrt, PATINDEX('%[^a-z]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING(@string, pos, LEN(@string))
    FROM (
           SELECT @string AS string , PATINDEX('%[a-z]%', @string) AS Pos
         ) d
     ) t

  RETURN @Alpha_Value;
END

Now use these functions inside your stored procedure to extract the Alphabet/Prefix bit and the Number bit and store them in the target columns.

Something like....

ALTER PROCEDURE [dbo].[traxs_Paybook_Data_Validate]

    @session_id varchar(30)
    @paybook_start_number varchar(30)
    @paybook_end_number varchar(30)

AS
  DECLARE @Start_Num_Prefix VARCHAR(100);
  DECLARE @End_Num_Prefix   VARCHAR(100);
  DECLARE @Start_Num_Numbers INT;
  DECLARE @End_Num_Numbers   INT;

SELECT  @Start_Num_Prefix   = dbo.fn_Extract_Alphabets(@paybook_start_number)
SELECT  @End_Num_Prefix     = dbo.fn_Extract_Alphabets(@paybook_end_number)
SELECT  @Start_Num_Numbers  = dbo.fn_Extract_Numbers(@paybook_start_number)
SELECT  @End_Num_Numbers    = dbo.fn_Extract_Numbers(@paybook_end_number)


..... rest of your procedure and so on....

Upvotes: 1

Related Questions