remove a particular character from varchar while retrieving data using query

Question

I want to remove a particular character while retrieving a varchar from a table. The query looks like this:

SELECT ServiceName,
       case isNumeric(Ramusage) 
         when 1 then 
           cast ( ltrim ( rtrim ( ramusage )) as int )
         else 
           0 
       end as test,
       priority
  FROM ProcessInfo

Here the problem is the ramusage is a varchar and it stores values like 12,500K, 4,321K.

Here i want to remove the k from the entry and display the number alone.

Answer 1

substr( ltrim( rtrim( ramusage ) ), length( ramusage ) - 1)

edit: replace, as mentionned in the answers below, is prolly better.

Answer 2

Use REPLACE

DECLARE @ProcessInfo TABLE(
     ServiceName VARCHAR(MAX),
     Ramusage VARCHAR(MAX),
     priority INT
)

INSERT INTO @ProcessInfo (ServiceName,Ramusage,priority)
SELECT 'TEST','12,123K',1

SELECT ServiceName,
       case isNumeric(REPLACE(REPLACE(Ramusage,'K',''),',','')) 
         when 1 then 
           cast ( ltrim ( rtrim ( REPLACE(REPLACE(Ramusage,'K',''),',',''))) as int )
         else 
           0 
       end as test,
       priority
  FROM @ProcessInfo

what we have done is to create a CLR function to take a string amd remove all non numeric items

[Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RemoveNonNumeric(SqlString strValue)
    {
        string strNew = strValue.ToString();

        char[] chrArray = strNew.ToCharArray();

        strNew = null;

        for (int iChar = 0; iChar < chrArray.Length; iChar++)
        {
            if (Char.IsNumber(chrArray[iChar]))
                strNew += chrArray[iChar];
        }

        // Put your code here
        return new SqlString(strNew);
    }

Answer 3

You want to use the REPLACE function:

REPLACE(ramusage, 'k', '')

Reference:

• SQL Server
• Oracle
• MySQL