Replace semi-colon with new line

Question

Post reply

Replace semi-colon with new line

simflex-897410

Hall of Fame

Points: 3881
More actions
October 28, 2010 at 9:36 am

#225581

Hello again experts,
I am trying to replace semi-colon (;) for some values for a field name.
For instance, CauseID CauseName
1 acting lesson;Judging results;nothing yet
2 pizzaz;merciless;Ondulate
When semi-colon is replaced with newline, the result should look like this:
1 acting lesson
1 Judging results
1 nothing yet
2 pizzaz
2 merciless
2 Ondulate
How possible can I do this?
I tried this which doesn't work:
SELECT causeid, REPLACE(causename, ';', CHAR(13)) from...
Thanks a lot for all the assistance here.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

http://www.sqlservercentral.com/scripts/Miscellaneous/30225/[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

The delimited split function linked above is not necessarily the best, it is loop based.

Do a search of this site for delimited split. I know I have posted a function on several threads myself.

I can't do much more from my BlackBerry, but once I get home I can provide the code directly if you haven't found anything else.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Lynn Pettis (10/28/2010)
The delimited split function linked above is not necessarily the best, it is loop based.
Do a search of this site for delimited split. I know I have posted a function on several threads myself.
I can't do much more from my BlackBerry, but once I get home I can provide the code directly if you haven't found anything else.

I'm getting too soft... I figure this a one 1 off event and didn't bother doing a 5th search to find one on here, but I totally agree with you that set based will be faster than looped.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

My function is also written as an in-lineTFV, combined with the cross apply and strings less than or equal to 8000 bytes, it works really quick.

I know that there may be better routines out there, there was a thread dedicated to working on this specifically as my routine slows when the string length exceeds 8000 bytes.

sturner SSC-Insane Points: 22411 More actions · Answer 5

Depending on why you need to do this, this might be useful:

SELECT @causename = causename from...

print replace(@causename,';',char(13)+char(10))

The probability of survival is inversely proportional to the angle of arrival.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 6

Thanks to all of you for your *ALWAYS* helpful attitude.

I really appreciate it.

I am still searching for forum for some helpful hints.

Hi Sturner, I indicated at the top why I am doing this.

So that rather than have values like this:

1 blah; blah;blah

2 moreblah;moreblah;moreblah

we would like it laid out more like:

1 blah

2 moreblah

etc

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 7

Check out the DelimitedSplit8k function here:

http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589

It'll do exactly what you need.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA