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 |
|
sql_basic
Starting Member
9 Posts |
 Posted - 2012-04-05 : 08:33:55
|
| I need some help reagarding substring fuction.I am inserting data in sql table from a .txt file. I read the file line by line and insert it in table. Now I want to replace the content of the line with some other values how do i do it. See below exampleI have a string variable @sel= '1234','defgh','bhhhy','Craig 345.999','rrer','345.999'now i want to replace the items 'Craig 345.123' and '345.123' with 'Craig 88.123' and '88.123' in the string.Kindly note that string is dynamic so it wont contain same values though the there position in string will remain sameKindly share your thoughts on this. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-05 : 11:42:03
|
| If you have a consistent pattern that you can identify that you want to replace, then you can do it (for the most part). But your example really is insufficient to determine that. Is the part that you want to replace always preceded by a space and followed by a single quote?Also is the variable @sel a collection of comma-separated tokens, or do they also have single quotes in them? |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-04-05 : 11:48:21
|
| I'm confused. Your variable @sel seems to hold a series of strings. The value you want to replace does not seem to appear in any of the data you listed. From the data you have listed can't you just replace the common substring?[CODE]select REPLACE(@sel, '345.123', '88.123')[/CODE]I have probably missed the mark on this but if you can elaborate a bit on the problem to be solved, I'm sure that someone can point towards a solution.HTH=================================================There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE) |
|
|
|
sql_basic
Starting Member
9 Posts |
Posted - 2012-04-05 : 12:12:16
|
quote:
Originally posted by sunitabeck
If you have a consistent pattern that you can identify that you want to replace, then you can do it (for the most part). But your example really is insufficient to determine that. Is the part that you want to replace always preceded by a space and followed by a single quote?Also is the variable @sel a collection of comma-separated tokens, or do they also have single quotes in them?
Apologies its just comma seperated and not single quotessuppose string is like this @sel=1234,defgh,bhhhy,1007 SUBLEDGER USD User 123.693028,rrer,345.999first i need to identify that at position of 4th comma i.e,1007 SUBLEDGER USD User 123.693028 there is a numeric value (in this case it is 12.693028 but it could be anyhting its not constant)and if that numeric value is there then i need to check the lenght of taht numeric value if it exceeds 3 decimal palces to left of decimal if it does then repalce it with 99.693028 so that particular postion in the string @sel should look like this 1007 SUBLEDGER USD User 99.693028I hope its better and clearer example than earlier |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-05 : 13:19:24
|
| why not dump everything onto a staging table from file and do your tranformations/corrections before moving to final table?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Substring and replace fuctionality