locked
insert carriage return in the middle of the text RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello ,

    I have a

    col1         col2                       col3                                       col4

    306   2012-08-07    15:01:59.763     02/23/2012SRN5289666 865113231016102/23/2012SRN5289666 865

    what i would like to have in database is

    for col4 is

    need to insert a carriage return in front of SRN so the output of the result should be

    306 2012-08-07 15:01:59.763          02/23/2012
                                                      SRN5289666 865113231016102/23/2012 
                                                      SRN5289666 865

    How can i do this Please suggest how i can do this.

    Thanks

    Madhavi

     

    Wednesday, September 19, 2012 3:56 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    This is either a front-end issue as not every front-end uses a CR, some need a CRLF others only a LF or it is a Normalization issue (atomic data).

    But when you really think it is a good idea to solve this in a query, then it's a simple REPLACE():

    DECLARE @Sample TABLE ( Payload NVARCHAR(255) ) ;

INSERT INTO @Sample VALUES 
	( '865113231016102/23/2012SRN5289666 865' );

SELECT	*,
		REPLACE(Payload, 'SRN', CHAR(13) + 'SRN')
FROM	@Sample;



    • Edited by Stefan Hoffmann Wednesday, September 19, 2012 4:25 PM D'oh! CR:)
    • Proposed as answer by SQL Novice 01 Wednesday, September 19, 2012 8:51 PM
    • Marked as answer by Kalman Toth Wednesday, September 26, 2012 1:15 AM
    Wednesday, September 19, 2012 4:06 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    This is either a front-end issue as not every front-end uses a CR, some need a CRLF others only a LF or it is a Normalization issue (atomic data).

    But when you really think it is a good idea to solve this in a query, then it's a simple REPLACE():

    DECLARE @Sample TABLE ( Payload NVARCHAR(255) ) ;

INSERT INTO @Sample VALUES 
	( '865113231016102/23/2012SRN5289666 865' );

SELECT	*,
		REPLACE(Payload, 'SRN', CHAR(13) + 'SRN')
FROM	@Sample;



    • Edited by Stefan Hoffmann Wednesday, September 19, 2012 4:25 PM D'oh! CR:)
    • Proposed as answer by SQL Novice 01 Wednesday, September 19, 2012 8:51 PM
    • Marked as answer by Kalman Toth Wednesday, September 26, 2012 1:15 AM
    Wednesday, September 19, 2012 4:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try:

    select Col1, Col2, col3, replace(col4, 'SRN', char(13) + 'SRN') as Col4 from myTable

    Look at the output in the text mode.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 19, 2012 4:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can do this in T-SQL like this

    --From the management studio menu make sure you have Query->Results->Results to Text selected.

update [table_name] set [column_name] = 
N'SRN5289666 865113231016102/23/2012 
SRN5289666 865'

select * from [table_name]

    Anand

    Wednesday, September 19, 2012 4:07 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I think the other answers will cause you to have an extra CR at the front of your text.

    Try this variant:

    update tbl
    set col4 = Left(col4,1) + Replace(Stuff(col4,1,1,''), 'SRN', Char(13) + 'SRN')

    It keeps the first 'SRN' on the first line.


    Wednesday, September 19, 2012 8:42 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    One question i have is when i ran the query the result did not show any carriage return, Do you think when i use this query for the report it will display it in a new line.

    Or when i export the results to text format it would show it.how would i see the changes .

    Thanks

    madhavi

    Thursday, September 20, 2012 4:48 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    In SSMS go to Query menu and change Results to text. You should be able to see carriage returns in this mode.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, September 20, 2012 4:51 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry i was running it with a different text search so thats why did not see the carriage return in result set.

    But i used the correct search string.

    Thanks

    Thursday, September 20, 2012 5:19 PM