September 16, 2011 at 1:09 am
Hello, I have a varbinary(max) variable containing data like
'abc_defg','deftg_ghvf_se','qud_rf_t'
I want to put these values in a temporary table like
Column_Value
------------
abc_defg
deftg_ghvf_se
qud_rf_t
How can we do it ?
September 16, 2011 at 1:18 am
you may want to split the text on comma and then removing single quotes.
see here how to write split function.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
September 16, 2011 at 1:29 am
Check out Jeff Moden's wonderful "DelimitedSplit8K" http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589
It will get you started on resolving your quest 😎
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 16, 2011 at 9:04 am
I tried this function with almost same code but seems its not giving all the records. Also after the first record all records are coming with a space in the first place. I tried ltrim and rtrim function but it didn't work either.
September 16, 2011 at 12:33 pm
sqlnaive (9/16/2011)
I tried this function with almost same code but seems its not giving all the records. Also after the first record all records are coming with a space in the first place. I tried ltrim and rtrim function but it didn't work either.
Almost the same code? did you use the one that ALZDBA posted from Jeff Moden or the other one with the loops in it?
I was able to use Jeff's code and it worked perfectly.
declare @WhyBinary varbinary(max)
set @WhyBinary = cast('''abc_defg'',''deftg_ghvf_se'',''qud_rf_t''' as varbinary(max))
select Item as Column_Value
from dbo.DelimitedSplit8K(replace(cast(@WhyBinary as varchar(max)), '''', ''), ',')
So I have to ask, why are you storing character data in a varbinary field???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply