|
alejo46
Yak Posting Veteran
Colombia
80 Posts |
 Posted - 06/28/2012 : 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
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 06/29/2012 : 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. |
 |
|
query to validate non_numeric data
