How to trim the first 3 characters of a string comparing with a master table of channel types via sql ?

Question

0.00/5 (No votes)

See more:

How to trim the first 3 characters of a string comparing with a Master Table of channel types via Sql ?

I am having a table where I am storing a prefix column for channel types SFTP/SWIFT (say sf_ for SFTP ,sw_ for SWIFT etc) in a Channel Table. I have Corporate Table where the corporateid (say Test will be stored as sf_Test /sw_Test based on Channel Type SFTP/SWIFT).

I want to trim the first 3 characters of the corporateId if it is having the prefix sw_/sf_ etc for Channel Table.

I am using the Scalar Function. But it is not working. I don't want to use cursor.

What I have tried:

I am using the Scalar Function. But it is not working.

SQL

/****** Object:  UserDefinedFunction [dbo].[fn_TrimCorporateId]    Script Date: 03/10/2016 10:18:31 ******/
/*** To Trim first 3 characters of Prefix Column newly added in Non_Mol_Channel_Identification */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select [dbo].fn_TrimPrefixChannelCorporateId('sf_1343')
ALTER FUNCTION [dbo].[fn_TrimPrefixChannelCorporateId]  
( 
	@CorporateId VARCHAR(13)  
)  
RETURNS NVARCHAR(100)  
AS  
  BEGIN   
        
	  DECLARE @TrimCorporateId varchar(13)
	  DECLARE @Prefix varchar(5)
	  
	 SELECT  @Prefix=Prefix
			 FROM NON_MOL_ChannelIdentification 
			 
	          RETURN @Prefix  
	 IF @Prefix <> ''
	 BEGIN 
	 SET  @TrimCorporateId= isnull( (SELECT RIGHT(CorporateId, LEN(CorporateId) - 3) AS MyTrimmedColumn
			 FROM dbo.CORPORATE
			 WHERE CorporateId = @CorporateId) ,'')  
	          
		
	 END		 
    RETURN @TrimCorporateId 
     
 END

Posted 10-Mar-16 1:29am

ranio

Updated 11-Mar-16 1:25am

CHill60

v3

Add a Solution

Comments

CHill60 10-Mar-16 7:31am

Don't just say "But it is not working" - explain what went wrong. Even better give a small set of sample data and some expected results to clarify what you mean

Maciej Los 10-Mar-16 8:34am

What you mean by saying "trim the first 3 characters"? In T-SQL, trim function reduces all spaces from text except single space between words. Seems, you want to return Test from sf_Test (reject prefix). Am i right?

[no name] 11-Mar-16 2:43am

If removing the sf_ from sf_Test is your requirement then you can create one function and pass the parameter to the function and get the required result from the function using SPLIT method. In this way you can able to get your required values.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

karttick · Answer 1 · 2016-03-11T01:25:00

there is no need of creating such complex function. You can do such thing in a simple manner :

SQL

	 DECLARE @Prefix varchar(5)
	  
	 SELECT  @Prefix=Prefix
	 FROM NON_MOL_ChannelIdentification 

	 select CorporateId= CASE WHEN @Perfix<>'' THEN right(CorporateId,len(CorporateId)-3) else CorporateId END
	 FROM dbo.CORPORATE
	 WHERE CorporateId = right(CorporateId,len(CorporateId)-3)
         OR CorporateId = CorporateId
-- use where condition according to your requirment

</pre>