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 |
|
under2811
Constraint Violating Yak Guru
366 Posts |
 Posted - 2013-04-24 : 15:32:08
|
| Splitting problem :(I have input string for procedure like below@str = '1,2,3~1100~Project Is Doing Good~' ~ is my separator.where I am splitting string and assigning parameters like @SecureID = 1,2,3@EmployeeID = 1100@WorkDescription = Project Is Doing Goodand use this in where clause to get desired output. This is working fine till user doesn’t enter character '~' itself in the @WorkDescription. e.g. Project Is Yellow ~ But Doing GoodTo avoid problem with separator and this character, what we are doing, we are putting '\' along with ~ in String itself to identify that user entered character is not separator. Now it looks like @str = '1,2,3~1100~Project Is Yellow \~ But Doing Good~'can anybody help me to get required output???@SecureID = 1,2,3@EmployeeID = 1100@WorkDescription = Project Is Yellow ~ But Doing GoodT.I.A |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-24 : 15:42:55
|
Replace occurences of \~ with an impossible character, do the split, and then in the results place it back to '\~'. For example:replace('1,2,3~1100~Project Is Yellow \~ But Doing Good~','\~',char(033)) |
 |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2013-04-24 : 15:47:36
|
| ThanksI was thinking like that but suppose if there might be chances that imposiible charater present it self in the string like1,2,3~1100~Project Is Yellow ~ But Doing Good!!!~if I place '\~' back to then it may happen 1,2,3~1100~Project Is Yellow ~ But Doing Good~~~ |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-24 : 15:55:50
|
| So very true! That is why I as picking something like ASCII 033 which is ESCAPE. Of course, if ESCAPE character could be in your text then you will run into the problem that you described.There are other alternatives, each of them requires more programming: A CLR split function using regex or a modified T-SQL split function (that probably would be loop based) that would skip over the escaped \~ etc. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-24 : 16:31:41
|
| Why not use the propper solution of a table-valued parameter instead? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-04-24 : 16:49:42
|
| Another alternative is to compose the tokens into an XML fragment in your client code and then use T-SQL to shred it. |
|
|
|
|
|
|
|
|
Splitting Problem..need help