Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

remove special character from column

I have a question.

There is a column named: Shipping Time, and it has a data type of varchar(32)
but when I run a select to see the values of this column, I see that the value is stored as:

sshot

I would like to learn if possible through asking this question,  how to convert, or replace that special character that I have not seen before.

For your help, thank you very much.
0
metropia
Asked:
metropia
3 Solutions
 
David L. HansenProgrammer AnalystCommented:
Will the returned good characters always be a single digit or could they be larger integers?
0
 
metropiaAuthor Commented:
the largest number would not pass the two digits
0
 
David L. HansenProgrammer AnalystCommented:
The the largest possible value would be 9?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
David L. HansenProgrammer AnalystCommented:
If so,
SUBSTRING(colName, 1, 1)
will do the job.
0
 
David L. HansenProgrammer AnalystCommented:
Did that work?
0
 
Scott PletcherSenior DBACommented:
To be safe, I suggest keeping all valid leading digits, and removing all characters after the last valid digit:


UPDATE dbo.tablename
SET [Shipping Time] = SUBSTRING([Shipping Time], PATINDEX('%[^0-9]%', [Shipping Time]) - 1, 100)
WHERE
    [Shipping Time] LIKE '%[^0-9]%'
0
 
rk_india1Commented:
If you have multiple special character the use the following

Using a series of REPLACE() functions

Here each REPLACE is used to change one unwanted character to a blank space or SPACE(0). The nesting of REPLACE function in recent version of the SQL Server product can go to hundreds of levels.

SELECT REPLACE( REPLACE( REPLACE( REPLACE( @str, '!', '' ), '#', '' ), '$', '' ), '&', '' );

If you have single special character the use the following

Using a REPLACE() function

Here each REPLACE is used to change one unwanted character to a blank space or SPACE(0). The nesting of REPLACE function in recent version of the SQL Server product can go to hundreds of levels.

SELECT REPLACE(@str, '#', '' )
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now