Post reply

String manipulation with a move

  • January 6, 2010 at 10:06 am

    #137187

    I have values like below

    100MG actep

    200MG ibuprof

    25MG acetp

    10% vial amox

    I need to change the data to look like

    100 MG actep

    200 MG ibuprof

    25 MG acetp

    10% vial amox

    Only the ones with MG have a space inserted. I was thinking replace but in this case I am actually doing an insert

  • January 6, 2010 at 10:15 am

    #1099743

    I have this idea which works row by row, but I need it to be inline sql

    DECLARE @drugName VARCHAR(20), @amp_position INT

    SELECT @drugName = '100MGWaldorf'

    SET @amp_position = CHARINDEX( 'MG', @drugName)

    SELECT @drugName = STUFF(@drugName,

    @amp_position, 2, ' ')

  • January 6, 2010 at 10:22 am

    #1099750

    timscronin (1/6/2010)

    I have this idea which works row by row, but I need it to be inline sql

    DECLARE @drugName VARCHAR(20), @amp_position INT

    SELECT @drugName = '100MGWaldorf'

    SET @amp_position = CHARINDEX( 'MG', @drugName)

    SELECT @drugName = STUFF(@drugName,

    @amp_position, 2, ' ')

    how about a REPLACE, like ColumnName = REPLACE(ColumnName 'MG',' MG') WHERE ColumnName LIKE '%[0-9]MG%'?

    i think that would find all the MG's touching a number, and insert the space as part of it; you might want to add collation to it to force it to only do upper case, but it depends on your data; there could be '100mg propofol' in the data that you need to change.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 6, 2010 at 10:29 am

    #1099756

    Thanks that will work for the inline

Viewing 4 posts - 1 through 3 (of 3 total)

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