How to replace certain characters in coloumn during bulk update.

Question

How to replace certain characters in coloumn during bulk update.

nitinuniyal

SSC Eights!

Points: 997
More actions
October 27, 2013 at 9:57 am

#285243

Dears,
I am using the insert bulk command to upload date in one table using below code.
BULK INSERT Testdb.Sales.SalesOrderDetail
FROM '\\computer2\salesforce\dailyorderseworders.txt';
GO
While upload I want to replace ‘*’ in one column which is varchar.
Example: - My Column is 2345678987654321 and it can be any number, So I want to put ‘*’ after 6th number till 12th number, so the result will look like 234567******4321.
Do you think it’s possible?
Regards
NU

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Either change the source file before you import it or run an update afterwards.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nitinuniyal SSC Eights! Points: 997 More actions · Answer 2

Thanks,

That's what I thought to do.

Is that possible to provide the update query will give me the desired results ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Without table names, data types, column names, not really. Look up substring. Couple of those should together do what you want. Or you could use LEFT and RIGHT instead of substring.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

nitinuniyal (10/27/2013)
While upload I want to replace ‘*’ in one column which is varchar.
Example: - My Column is 2345678987654321 and it can be any number, So I want to put ‘*’ after 6th number till 12th number, so the result will look like 234567******4321.

are these credit/payment card numbers that you are attempting to obfuscate by any chance??...just interested??

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

J Livingston SQL (10/27/2013)
are these credit/payment card numbers that you are attempting to obfuscate by any chance??...just interested??

I seriously hope not. If they are, they should be getting encrypted, not obfuscated.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass