Need to remove a dash, Stupid, but I cant remember how! 1

[dpwsmw]

Does anyone know how to remove a dash from a field?

for instance this is
tblApHistDetail.glacct
6169-90
6169-90
6169-90
6169-90
1412-00
6122-10
6122-10
5220-01
5220-01

I want it to be this way
616990
616990
616990
616990
141200
612210
612210
522001
522001

I thought an Update - SET would work, but I cant figure out the Syntax.. Brain Fart!! Anyone?

 

[fluteplr]

update mytable
set myfield = repace(myfield,'-','')

 

[Vinodk]

Hi,

replace is one option available ...
Like

field = repace(field,'-','')

You can use the Case Statement construct also to achieve this (provided you know the length of the string):

DECLARE @BadStr VARCHAR(30)
SET @BadStr = '6169-90'

SELECT CASE WHEN SUBSTRING(@BadStr, 1, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 1, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 2, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 2, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 3, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 3, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 4, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 4, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 5, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 5, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 6, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 6, 1) ELSE '' END +
CASE WHEN SUBSTRING(@BadStr, 7, 1) LIKE '[0-9]'
THEN SUBSTRING(@BadStr, 7, 1) ELSE '' END

Or can use a User-defined function to remove unwanted charaters like:

Drop FUNCTION dbo.onlydigits (@Value AS VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%', @Value) > 0
SET @Value = REPLACE(@Value,
SUBSTRING(@Value,PATINDEX('%[^0-9]%', @Value),1),'')
RETURN @Value
END
GO

SELECT dbo.onlydigits('9vinf2%887^f8i76-*K')

HTH,
Vinod Kumar