SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Splitting Problem..need help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

under2811
Constraint Violating Yak Guru

India
359 Posts

Posted - 04/24/2013 :  15:32:08  Show Profile  Reply with Quote
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 Good

and 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 Good

To 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 Good

T.I.A

Edited by - under2811 on 04/24/2013 15:38:23

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/24/2013 :  15:42:55  Show Profile  Reply with Quote
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))
Go to Top of Page

under2811
Constraint Violating Yak Guru

India
359 Posts

Posted - 04/24/2013 :  15:47:36  Show Profile  Reply with Quote
Thanks

I was thinking like that but suppose if there might be chances that imposiible charater present it self in the string like

1,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~~~

Edited by - under2811 on 04/24/2013 15:48:24
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/24/2013 :  15:55:50  Show Profile  Reply with Quote
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.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/24/2013 :  16:31:41  Show Profile  Reply with Quote
Why not use the propper solution of a table-valued parameter instead?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 04/24/2013 :  16:49:42  Show Profile  Reply with Quote
Another alternative is to compose the tokens into an XML fragment in your client code and then use T-SQL to shred it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000