Remove specific character from string (SQL Server 2012)

Question

I have a view from which I want to remove a specific character in one of the columns. Specifically the character 'B' from the 'Fund' column below.

I tried using the following version of TRIM

  SELECT 
       TRIM('B' FROM [Port_Ticker]) as "Fund"
      ,[BENCH_Ticker] as "Index ID"
      ,format(cast([VALUE_DATE] as Date),'dd/MM/yyyy') as "Updat"
      ,([Port_Risk_Contrib] / 10000000) as "StDev Fund"
      ,([Active_risk_contrib] / 10000000) as "TE"
      ,([BENCH_RISK_CONTRIB] / 100) as "StDev BM"
  FROM [DM_PORTFOLIO_ANALYSIS].[basedata].[PortfolioRiskFigures]
  where [BLOCK_FACTOR] = 'Total'
  and [Port_ticker] = 
  'B1023'
  order by [VALUE_DATE] asc

Which gives me the error

Msg 156, Level 15, State 1, Line 3. Incorrect syntax near the keyword 'FROM'.

I recommend against FORMAT, it's an awfully slow function. You would be far better off using CONVERT and a style code. So format(cast([VALUE_DATE] as Date),'dd/MM/yyyy') could be change to CONVERT(varchar(10),[VALUE_DATE],103) (assuming that VALUE_DATE is a datetime datatype, which I see no reason why it would be as it's storing a date. :) ) — Thom A
– Thom A ♦, Commented May 28, 2019 at 8:42
TRIM is available since SQL Server 2017. So your version doesn't support TRIM: learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql — Sebastian Brosch
– Sebastian Brosch, Commented May 28, 2019 at 8:42
Example of how slow FORMAT is compared to CONVERT: db<>fiddle — Thom A
– Thom A ♦, Commented May 28, 2019 at 8:52

M. Kanarkowski · Accepted Answer · 2019-05-28 08:37:16Z

3

You can use replace() to do this. In this case it will search for 'B' and replace it with an empty string -> ''. Please note that this function will replace all 'B' from this column.

 SELECT 
       REPLACE([Port_Ticker], 'B', '') as "Fund"
      ,[BENCH_Ticker] as "Index ID"
      ,format(cast([VALUE_DATE] as Date),'dd/MM/yyyy') as "Updat"
      ,([Port_Risk_Contrib] / 10000000) as "StDev Fund"
      ,([Active_risk_contrib] / 10000000) as "TE"
      ,([BENCH_RISK_CONTRIB] / 100) as "StDev BM"
  FROM [DM_PORTFOLIO_ANALYSIS].[basedata].[PortfolioRiskFigures]
  where [BLOCK_FACTOR] = 'Total'
  and [Port_ticker] = 'B1023'
  order by [VALUE_DATE] asc

edited May 28, 2019 at 8:37

answered May 28, 2019 at 8:35

M. Kanarkowski

2,20510 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2019-05-28 08:42:09Z

3

The Trim() function was introduced in 2017 version.
Naturally, you can't use it in older versions.

There are several ways this can be done, by using replace as M. Kanarkowski demonstrated in his answer, or any of these options:

stuff: STUFF([Port_Ticker], 1, 1, '') As "Fund",
substring: SUBSTRING([Port_Ticker], 2, LEN([Port_Ticker])) As "Fund"
Right: RIGHT([Port_Ticker], LEN([Port_Ticker])-1) As "Fund"

answered May 28, 2019 at 8:42

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Collectives™ on Stack Overflow

Remove specific character from string (SQL Server 2012)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related