query to validate non_numeric data

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

Transact-SQL (2000)

query to validate non_numeric data

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-06-28 : 17:54:57

Good afternoon

ive got 2 questions:

1.ive got a table loke thos:

CDR_NUMBER B_NUMBER
4 0713888
3 +1433142019162
2 +1433142019162
6 +1433142019162
6 +1433142019162

i need to identify non_numric data and then remove the non numeric character ie (+)

so the query is:

select CDR_NUMBER,B_NUMBER,ISNUMERIC(B_NUMBER)Validate
,case when isnumeric(B_NUMBER) <> 1 then substring(B_NUMBER,2,16) else (B_NUMBER) end "Tipo de Dato"
from TEMP_ICT_DW

but in the output in the B_NUMBER column doesnt remove the character,
it remains the same

+1433142019162
+1433142019162
+1433142019162
+1433142019162
+1433148638052
+1433148638052

2.what exactly the below query does with a nested replace and trim functions?:

SELECT
CONVERT (VARCHAR (6),ICS.DATE_PROCESSED, 112) YEAR_MONTH
,LEFT (REPLACE (
REPLACE (
RTRIM ( LTRIM ( ICS.B_NUMBER ) )
,''+''
,''''
)
,''0315R''
,''''
)
,18 ) NUM_MOVIL1

Id appreciate your help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-29 : 05:05:02

1.
You can just do this if your data is as you have posted:
select
replace(B_NUMBER,'+','')
from table

2.
What it does:
- remove trailing and leading spaces
- replace the +-sign by nothing
- replace 0315R by nothing
- from that result take the leftmost 18 characters

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-29 : 06:16:52

yoou might want to run
select *
from tbl
where B_NUMBER like '%[^0-9]%'
to find all rows which contain non digit characters.

Here's a routine for removing eveything that is not a digit
http://www.nigelrivett.net/SQLTsql/RemoveNonNumericCharacters.html

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

alejo46
Posting Yak Master

157 Posts

Posted - 2012-07-03 : 20:11:28

Thanks a lot, both of you for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources