August 27, 2008 at 8:20 am
I am trying to write a query that will show name, and CCNUM, however within the table I am working with the CCNUM is stored as a GUID. Is there anyway to 'convert' the GUID to the XXXXXXXXXXXX1111 where the last 4 numbers are the actual CC#'s last 4 digits.
I've been searching and searching and trying differnt Casts/Converts but without any luck.
Thanks
Steve
August 27, 2008 at 8:36 am
How about something like this.
Declare @tst uniqueidentifier
Set @tst = newid()
Select @tst
Select Right(stuff(@tst,1,32,replicate('X',32)),16)
August 27, 2008 at 8:52 am
that creates a new id, it does not give me the last 4 digits of the actual credit card number...again the cc num is already stored as a binary GUID.....
Thanks again
August 27, 2008 at 8:59 am
The script that creates the new guid is just for example. Use the logic in the select statment on your credit card field.
Select Right(stuff(CCNUM,1,32,replicate('X',32)),16)
From YourTable
August 27, 2008 at 9:07 am
🙂 I had tried that as well...and was returned 16 X's...
working with GUID's are new too me...
August 27, 2008 at 9:56 am
Try this. What is the datatype of the CCNum Column? It does not sound like a uniqueidentifier.
Select Right(stuff(CCNUM,1,12,replicate('X',12)),16)
From YourTable
August 27, 2008 at 10:05 am
it's binary (22)...so is it just a binary number, whats that called m5 something...is there a different conversion for that?
August 27, 2008 at 10:16 am
If you mean it's MD5 encrypted, you can't unencrypt it. However I don't think yours is MD5 encrypted as it would required varbinary of at least 32 bytes. Also, there's no point in encrypting a credit card number as MD5.
Try this and see if what it returns is legible...if not then it is probably encrypted, and you will have to find out how it was encrypted....
select top 100 cast(CCNUM as varchar(22)) from yourtable
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply