CODEWITHSUNDEEP
sqlsql-serversql-server-2012
BlackBear
BlackBear

Reputation: 385

Remove specific character from string (SQL Server 2012)

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.

enter image description here

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'.

Upvotes: 1

Views: 8777

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

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"

Upvotes: 3

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

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

Upvotes: 3

Related Questions