Removing a special character from a character field.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Removing a special character from a character field.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-19 : 08:29:29

Johnson writes "1. Our Item Master table has a field named description.

2. The description data type is of 30 CHARACTER.

3. These descriptions contain special characters.

4. We would like to remove only the special characters keeping the rest of the description intact.

5. For e.g. the description could be "Cotton Hand Gl¢Àoves"

6. We need to remove "¢À" character from the above.

7. The final result should be "Cotton Hand Gloves".

Hope to hear soon.

Thanks
Johnson."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-19 : 08:32:53

Select Replace(col,'¢À','') from yourTable

Madhivanan

Failing to plan is Planning to fail

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-06-19 : 11:27:37

if you have more special characters in your data and you don't know where those characters are coming from the you can create a funtion to remove those special characters and it will take care of that. I think i have done it in the past and i might have something on it.
I will try to look.

sqldev80
Yak Posting Veteran

68 Posts

Posted - 2006-06-19 : 11:30:45

Go to:

http://www.miningtools.net/
http://www.miningtools.net/regexp/default.aspx

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-19 : 12:33:26

Here an SQL function you can use:
--Author: Perez, Jeffrey
--Desc:
-- Remove invalid characters provided in the parameter.
--
IF (SELECT OBJECT_ID('dbo.fnRemoveInvalidChar')) IS NOT NULL DROP FUNCTION dbo.fnRemoveInvalidChar
GO

CREATE FUNCTION dbo.fnRemoveInvalidChar(@col VARCHAR(8000), @replacementChar VARCHAR(8000) = ' ')
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@position INT,
@finalCol VARCHAR(8000)

SELECT @position = 1
SELECT @finalCol = ''
WHILE @position <= DATALENGTH(@col)
BEGIN
IF ASCII(SUBSTRING(@col, @position, 1)) >= 33
BEGIN
SELECT @finalCol = @finalCol + SUBSTRING(@col, @position, 1)
END
ELSE
BEGIN
SELECT @finalCol = @finalCol + @replacementChar
END

SELECT @position = @position + 1
END

RETURN @finalCol
END
GO
--sample
SELECT dbo.fnRemoveInvalidChar('hi'+CHAR(30)+'char',' ')
GO

IF (SELECT OBJECT_ID('dbo.fnRemoveInvalidChar')) IS NOT NULL DROP FUNCTION dbo.fnRemoveInvalidChar
GO

May the Almighty God bless us all!

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-19 : 12:37:01

Sorry, I did not look more closely on your sample data. If you need to include all other printable characters, use the first function. Else, this should be the function that you need:

--Author: Perez, Jeffrey M
--Date: 6/19/2006
--Desc:
-- Remove the invalid characters.
IF (SELECT OBJECT_ID('dbo.fnRemoveInvalidChar')) IS NOT NULL DROP FUNCTION dbo.fnRemoveInvalidChar
GO

CREATE FUNCTION dbo.fnRemoveInvalidChar(@col VARCHAR(8000), @replacementChar VARCHAR(8000) = ' ')
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@position INT,
@finalCol VARCHAR(8000)

SELECT @position = 1
--SELECT @replacementChar = ' '
SELECT @finalCol = ''
--SELECT @col = 'hi'+CHAR(30)+' char'
--PRINT @col
WHILE @position <= DATALENGTH(@col)
BEGIN
IF ASCII(SUBSTRING(@col, @position, 1)) >= 33 AND ASCII(SUBSTRING(@col, @position, 1)) <= 126
BEGIN
--PRINT SUBSTRING(@col, @position, 1)
SELECT @finalCol = @finalCol + SUBSTRING(@col, @position, 1)
END
ELSE
BEGIN
SELECT @finalCol = @finalCol + @replacementChar
END

SELECT @position = @position + 1
END
--PRINT 'finalcol:' + @finalCol
RETURN @finalCol
END
GO

SELECT dbo.fnRemoveInvalidChar('hi'+CHAR(30)+'char',' ')
SELECT dbo.fnRemoveInvalidChar('Cotton Hand Gl¢Àoves',' ')
GO

IF (SELECT OBJECT_ID('dbo.fnRemoveInvalidChar')) IS NOT NULL DROP FUNCTION dbo.fnRemoveInvalidChar
GO

May the Almighty God bless us all!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 03:03:42

If you use Front end application then remove special characters there and send only VALID data to the table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources