String Function to remove symbols

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 2005 Forums

Transact-SQL (2005)

String Function to remove symbols

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-08-20 : 04:59:42

I have field in a SQL 2000 DB where users have entered a mix of characters and numbers.

I was wondering if there is a string function in T-SQL that removes characters (and any symbols) for the string to leave me with just the number values.

Ideally I would leave the character 'y' whenever it exists in the string (this is the only exception)

Any comments would be much appreciated
Thanks

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-08-20 : 05:03:04

Hi,

declare @vat as varchar(100)
set @vat='1234_xyuiiu)'

print substring(@vat,1,patindex('%[^0-9]%',@vat)-1)

check it out!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-20 : 05:10:05

see
http://www.nigelrivett.net/SQLTsql/RemoveNonNumericCharacters.html

You could probably do it in a single statement using a CTE if you wish.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 05:53:29

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 06:13:48

quote:
Originally posted by under2811

Hi,

declare @vat as varchar(100)
set @vat='1234_xyuiiu)'

print substring(@vat,1,patindex('%[^0-9]%',@vat)-1)

check it out!

Test this

declare @vat as varchar(100)
set @vat='g1234_xyuiiu)'

print substring(@vat,1,patindex('%[^0-9]%',@vat)-1)

Madhivanan

Failing to plan is Planning to fail

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-08-20 : 07:22:46

Thanks for all the help provided.
Its probably cos its new to me but the result set returned in the madhivanan eg returns no values while the @vat string begins with a character. Ideally, i'm looking for a function where I can pass the field that might contain eg 'ex:7820_work' and the return value is '7820' so as all potential characters and symbols are removed.

I can see there is some good debate on how best to achieve this and I’m currently trying to understand which might be the best option.
Apologies you may be able to tell - I’m new to T-SQL programming
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 08:27:04

If you send data from front end, you can filter there and send only valid data to 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