removing unwanted characters - SQL Server Forums

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

Other Forums

MS Access

removing unwanted characters

Author

Topic

epicphotoeye
Starting Member

4 Posts

Posted - 2006-08-31 : 14:44:29

I'm trying to remove all non-numeric characters from a field in my db. Currently I'm using:

DoCmd.RunSQL "update table set table.field1 = replace(field1, '-','')"
DoCmd.RunSQL "update table set table.field1 = replace(field1, ' ','')"
etc...

Is there a faster and/or more efficient way to do this so that I can say replace(field1, non-numeric, '')? I will have these query's running constantly so efficient would be preffered...

Thanks in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-31 : 14:47:35

Why not update your application so that it doesn't pass in these unwanted characters? Doing it this way would mean that you only have to update the data once.

Do the data validation in the presentation layer.

Tara Kizer

epicphotoeye
Starting Member

4 Posts

Posted - 2006-09-01 : 10:46:22

I will admit I am semi new to programming. I know a little bit of VB and even less of SQL. The way I'm working this is by finding a specific task I want to accomplish and researching how to do it. If you are speaking of doing the validation on import, I would like to, but I am unsure how to do that. I'm using vs2003 now to try getting around using access and using a sql server instead. A summary of what I'm dealing with, is mass importing of several types of excel and/or text delimited documents sorted by a header row. One of the columns I want to have everything but the numbers in this column removed. Once the multible files are imported, I'll need them exported as 1 file in .csv format.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-09-01 : 13:31:28

one pass....
update table set table.field1 = replace(replace(field1, '-',''), ' ','')

Then sort out your front end...prevention always better than cure.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 18:25:16

Create a module and write a function like this (in SQL, easy to rewrite as VBA). Then use SQL to

UPDATE Table SET Field1 = fnGetDigitsOnly(Field1)

CREATE FUNCTION dbo.fnGetDigitsOnly
(
	@Phone VARCHAR(30)
)
RETURNS VARCHAR(30)
AS

BEGIN
	DECLARE @Index TINYINT,
		@Temp VARCHAR(30),
		@Char CHAR

	SELECT	@Index = 1

	WHILE @Index <= LEN(@Phone)
		SELECT	@Char = SUBSTRING(@Phone, @Index, 1),
			@Temp = CASE WHEN @Char >= '0' AND @Char <= '9' THEN ISNULL(@Temp, '') + @Char ELSE @Temp END,
			@Index = @Index + 1

	RETURN @Temp
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 18:28:35

[code]Public Function fnGetDigitsOnly(Byval Value As String) As String

Dim lIndex As Long, sChar As String, sTemp As String

For lIndex = 1 To Len(Value)
sChar = Mid(Value, lIndex, 1)
If sChar >= "0" AND sChar <= "9" Then
sTemp = sTemp & sChar
End If
Next

fnGetDigitsOnly = sTemp

End Function[/code]UPDATE Table SET Field = fnGetDigitsOnly(Field)

Peter Larsson
Helsingborg, Sweden

epicphotoeye
Starting Member

4 Posts

Posted - 2006-09-06 : 11:16:27

thank you, I'll see how I can work that in.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources