CODEWITHSUNDEEP
sql-serversql-server-2008
Aparanjit
Aparanjit

Reputation: 41

Need to eliminate the last 4 characters in a string(varchar)

i am using a stored procedure, where it is taking policy number as parameter which is varchar. I need to eliminate the last 4 characters of the policy number when we retrive from the tables. But the data for policy numbers is not consistent, so I am confused how to use the logic for this. The sample policy numbers are:

  1. KSDRE0021-000
  2. APDRE-10-21-000
  3. KSDRE0021
  4. APDRE-10-21

These are four formats where policies are there in our tables.For some policies there is no tailing end '-000', so that is the challenging part. Now, I need to eliminate the tailing part '-000' from the policies when I retrieve the data from tables.

This is the sample code, which is pulling the policy data from tables.

Create Proc usp.dbo.policydataSP @policy_num varchar(18)
AS
Begin
Select * from policy_table pt
where pt.policy_num = @policy_num
End

Upvotes: 1

Views: 84

Answers (3)

dana
dana

Reputation: 18125

STEP 1: Create a User Defined Function to normalize a policy number.

create function dbo.normalize_policy_num
    (@policy_num varchar(100))
returns varchar(100)
as
begin

    -- replace trailing digits
    if (@policy_num like '%-[0-9][0-9][0-9]')
        set @policy_num = left(@policy_num, len(@policy_num) - 4)

    -- replace remaining hyphens
    set @policy_num = replace(@policy_num, '-', '')

    return @policy_num

end

What this essentially doing is stripping off the trailing '-000' from policy numbers that contain the pattern, then removing remaining hyphens. This function seems to work on your supplied policy numbers:

-- returns: KSDRE0021
select dbo.normalize_policy_num('KSDRE0021-000')

-- returns: APDRE1021
select dbo.normalize_policy_num('APDRE-10-21-000')

-- returns: KSDRE0021
select dbo.normalize_policy_num('KSDRE0021')

-- returns: APDRE1021
select dbo.normalize_policy_num('APDRE-10-21')

STEP 2: Modify your SP as follows:

create proc usp.dbo.policydataSP
    @policy_num varchar(18)
as
begin

select
    dbo.normalize_policy_num(pt.policy_num) as normalized_policy_num,
    pt.*
from policy_table pt
where dbo.normalize_policy_num(@policy_num) = dbo.normalize_policy_num(pt.policy_num)

Note: If you are able to modify the table schema, you could add a persisted computed column using the UDF specified above. If you add an index to it, queries will run much faster. However, there will be some penalty for inserts, so there is a trade-off.

Upvotes: 2

md4
md4

Reputation: 1669

If you only want to strip -000 when returning results:

select case right(policy_num, 4)
            when '-000' then left(policy_num, len(policy_num) - 4)
            else policy_num end as policy_num
  from policy_table pt
 where pt.policy_num = @policy_num

If you want to strip any 3-digit value following a dash:

select case when policy_num like '%-[0-9][0-9][0-9]' then left(policy_num, len(policy_num) - 4)
            else policy_num end as policy_num
  from policy_table pt
 where pt.policy_num = @policy_num

Upvotes: 0

Geoduck
Geoduck

Reputation: 9005

this is probably your best bet. Match the policy number up to the length of the requested parameter:

Create Proc usp.dbo.policydataSP 
    @policy_num varchar(18) 
AS 
Begin 
    Select * from policy_table pt where LEFT(len(@policy_num),pt.policy_num) = @policy_num 
End

Upvotes: 0

Related Questions