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 |
|
venkat.v
Starting Member
4 Posts |
 Posted - 2009-08-26 : 01:06:42
|
| Hi,could somebody plz help me out in changing the pattern ( say SSN number) to 'x'i.e., i need to pass a big sentence which contains SSN number in it.Ex :"Hi my name is richard.My SSN no is 123-45-6789"and it should be changed to "Hi my name is richard.My SSN no is xxx-xx-xxxx"I have done by creating function using Patindex but could somebody help me to do this using SSIS.and especially i need to update a whole column which consists of text mentioned above with the change in the pattern..thanks in advance |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-08-26 : 04:15:52
|
| Why do you need to do this in SSIS?I'm sure you know, but it's not a good idea to store the data like this.You can do this with the STUFF and PATINDEX functions in SQL:declare @MyStrTable TABLE (MyStr varchar(200) ) INSERT INTO @MyStrTable VALUES ('Hi my name is richard.My SSN no is 123-45-6789')INSERT INTO @MyStrTable VALUES ('Hi my name is ann.My SSN no is 000-45-6789. And i')INSERT INTO @MyStrTable VALUES ('Hi my name is james.My SSN no is 12w-45-6789') --invalidINSERT INTO @MyStrTable VALUES ('Hi my name is henry.My SSN no is 123-234-6789') --invalidselect MyStr , STUFF(MyStr , patindex('%[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]%' , MyStr) , 11 , 'xxx-xx-xxxx')from @MyStrTable |
 |
|
|
|
|
|
changing pattern using SSIS