July 26, 2005 at 5:51 am
Need SQL to accommodate the following data migration for Credit Card numbers:
Source column: Col_A nvarchar(25) null
Target column: Col_B nvarchar(100) null
Need to INSERT the contents of Col_A into Col_B with the following spec's:
-Only populate the last 4 characters of SOURCE Col_A into Col_B
-Prefix the 1st 12 characters of Col_B w/ asterisks '*'
-For NULL source columns, persist NULLS into the target Col_B
FYI - the SOURCE Col_A may contain virtually anything. Persist this w/ the rules above.
Examples:
Col_A Col_B
1234 5678 4321 8765 becomes ************8765
1234567887654321 becomes ************4321
ABDC12345678 becomes ************5678
P12345 becomes ************2345
P 535 becomes ************ 535
CO5376 becomes ************5376
BK 1-543/123 becomes ************/123
{NULL values} persist as {NULL values}
{spaces} persist as {NULL values}
July 26, 2005 at 6:08 am
How 'bout something like UPDATE tableA SET ColB = REPLICATE('*', 12) + RIGHT(RTRIM(LTRIM(ColA)), 4)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 26, 2005 at 7:09 am
doesn't take care of the nulls and spaces though...
this might do the trick..
INSERT INTO tblDestination
SELECT CASE
WHEN LEN(cardNum) > 0 then REPLICATE('*', 12)+ RIGHT(cardNum, 4)
ELSE NULL
END
FROM tblSource
**ASCII stupid question, get a stupid ANSI !!!**
July 26, 2005 at 10:53 am
lookup the STUFF() function in BOL! Should do the job for u!
July 26, 2005 at 10:57 am
thx all. Problem resolved!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply