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.
| 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 |
|
|
|
|
|
|
query to validate non_numeric data