CODEWITHSUNDEEP
sql
viswanathan
viswanathan

Reputation:

Remove a particular character from varchar while retrieving data using query

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

Upvotes: 1

Views: 6782

Answers (3)

Natrium
Natrium

Reputation: 31174

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

edit: replace, as mentioned in the other answers, is probably better.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

You want to use the REPLACE function:

REPLACE(ramusage, 'k', '')

Reference:

Upvotes: 5

Adriaan Stander
Adriaan Stander

Reputation: 166396

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);
    }

Upvotes: 2

Related Questions