asked on

SQL Query force return carriage in string at certain keyword

I have a field with strings and would like to force a return carriage. Is that possible?

Example:

Updated 8/25/2015: Tested and cleaned

To be changed to:

Updated 8/25/2015:
Tested and cleaned

So hoping to do a carriage return at the ":" symbol. Any ideas?

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

If you want to add the CR only for the first :, you can do this:

SELECT STUFF(column_name, CHARINDEX(':', column_name), 0,
--the CASE is only if you need to test whether a colon actually appears in the data for not
CASE WHEN CHARINDEX(':', column_name) > 0 THEN CHAR(13) ELSE '' END)
FROM table_name
...

holemania

ASKER

ScottPeltcher,

Looks like your solution will work, but my colon doesn't stay in the first line, but went to the second line. It's like this now.

Updated 8/25/2015
:Tested and cleaned

Scott Pletcher

Sorry, I didn't adjust the starting byte properly:

SELECT STUFF(column_name, CHARINDEX(':', column_name) - 1, 0,
--the CASE is only if you need to test whether a colon actually appears in the data for not
CASE WHEN CHARINDEX(':', column_name) > 0 THEN CHAR(13) ELSE '' END)
FROM table_name

Vitor Montalvão

holemania, do you still need help with this question?

holemania

ASKER

Thanks.