Tags: 1pages, character, characters, column, database, f5pages, field, microsoft, mysql, oracle, printed, server, special, sql, table, thedata, thistable

Remove special characters from column/field

On Database » Microsoft SQL Server

3,086 words with 2 Comments; publish: Sat, 07 Jun 2008 20:38:00 GMT; (250250.00, « »)

I have a column that has a special character ( that I want to remove. The

data in the table looks like this:

Table name F5

pages printed: 1

pages printed: 15

pages printed: 150

All I am interested in is the actual number, nothing before and including

the :

I created a view using the following query to crop the field:

Select substring(F5, DATALENGTH(F5)-2,5) as Printed

and then it returns:

: 1

15

150

Is there a way to remove the first part of the string 'pages printed:'

so all I am left with is the actual numbers?

OR remove only the : and the spaces from the view I created?

Sebastian Cavignac

Network Administrator

Salt River Materials Group

928.639.8095

scavignac.sqlserver.questionfor.info.hotmail.com

All Comments

Leave a comment...

  • 2 Comments
    • You could do a simple REPLACE. For example,

      SELECT REPLACE(ColName, 'pages printed: ', SPACE(0)) FROM TableName

      HTH,

      Vyas, MVP (SQL Server)

      SQL Server Articles and Code Samples .sqlserver.questionfor.info. http://vyaskn.tripod.com/

      "Sebastian Cavignac" <SebastianCavignac.sqlserver.questionfor.info.discussions.microsoft.com> wrote in

      message news:29712973-EAF2-4C34-9175-F0854EB97771.sqlserver.questionfor.info.microsoft.com...

      >I have a column that has a special character ( that I want to remove. The

      > data in the table looks like this:

      > Table name F5

      > pages printed: 1

      > pages printed: 15

      > pages printed: 150

      > All I am interested in is the actual number, nothing before and including

      > the :

      > I created a view using the following query to crop the field:

      > Select substring(F5, DATALENGTH(F5)-2,5) as Printed

      > and then it returns:

      > : 1

      > 15

      > 150

      > Is there a way to remove the first part of the string 'pages printed:'

      > so all I am left with is the actual numbers?

      > OR remove only the : and the spaces from the view I created?

      >

      > --

      > Sebastian Cavignac

      > Network Administrator

      > Salt River Materials Group

      > 928.639.8095

      > scavignac.sqlserver.questionfor.info.hotmail.com

      #1; Sat, 07 Jun 2008 20:39:00 GMT
    • That worked! Thanks for your help.

      Sebastian Cavignac

      Network Administrator

      Salt River Materials Group

      928.639.8095

      scavignac.sqlserver.questionfor.info.hotmail.com

      "Narayana Vyas Kondreddi" wrote:

      > You could do a simple REPLACE. For example,

      > SELECT REPLACE(ColName, 'pages printed: ', SPACE(0)) FROM TableName

      >

      > --

      > HTH,

      > Vyas, MVP (SQL Server)

      > SQL Server Articles and Code Samples .sqlserver.questionfor.info. http://vyaskn.tripod.com/

      >

      > "Sebastian Cavignac" <SebastianCavignac.sqlserver.questionfor.info.discussions.microsoft.com> wrote in

      > message news:29712973-EAF2-4C34-9175-F0854EB97771.sqlserver.questionfor.info.microsoft.com...

      >

      >

      #2; Sat, 07 Jun 2008 20:40:00 GMT