| Author |
Topic |
|
dtrivedi
Posting Yak Master
153 Posts |
 Posted - 2010-09-15 : 08:53:03
|
| I need a sql statement that isolate sout any records that could have a 13 to 16 digit number anywhere in the column the column is 40 character field. This statement is what I have so far that I found but it's not running select CSCMT2, translate(translate(CSCMT2,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ,./?#@$%^&*()_+=-`\|:;"', ' '),' ','')from AARDTAMAC.AARCSTM MSTwhere length(translate(translate(CSCMT2,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ,./?#@$%^&*()_+=-`\|:;"', ' '),' ','')) > 15 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-15 : 09:00:33
|
TRANSLATE() isn't known in SQL SERVER.Maybe this is the wrong forum?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-15 : 09:02:40
|
| I come from an oracle background...thats' what it is therewhat is the command for translate or to find isolate out any records that can show me the digits? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-15 : 09:07:45
|
Try:where CSCMT2 like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-15 : 09:18:00
|
| I am getting this errorOLE DB provider "IBMDA400" for linked server "MAC" returned message "SQL0104: Token . was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.Cause . . . . . : A syntax error was detected at token .. Token . is not a valid token. A partial list of valid tokens is FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token .. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.".Msg 7321, Level 16, State 2, Line 1An error occurred while preparing the query " select CSCMT2 from from AARDTAMAC.AARCSTM where CSCMT2 like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-15 : 09:24:05
|
| nevermind i figured the error out....it's running but no records are found so far |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-15 : 09:33:22
|
Show us the query that is retrieving no records and give us table structure and some sample data.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-15 : 09:41:59
|
| this is the queryselect CSCMT2 from AARDTAMAC/AARCSTM where CSCMT2 like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] %' and for hte sample data its a comments filed so it has 40 sting character valueanything from a blank value to a credit card number to an emaili need to isolate all the credit card numbers |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-15 : 09:59:02
|
There is a carriage return/line feed to remove.Also you can try to use replace() instead of translate().
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
dtrivedi
Posting Yak Master
153 Posts |
Posted - 2010-09-15 : 10:12:18
|
| hmm the replace worked but its showing all the records...even the one with the text rather than just credit card numbersis there anyway to isoloate credit card #s? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-15 : 10:35:40
|
replace the LENGTH() by LEN()
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
isolate records