problem selecting varchar

  • hi

    I have a varchar(50) column , but in the application the field is restricted to 15 characters. So, I have to return only 15 chars, but I don't have to truncate the result. An example to help looks like this :

    declare @a varchar(50)

    set @a = 'michael the greatest'

    select substring(@a,0,15)

    This script returns

    michael the gr

    But the result should be like

    michael the

    The last word should not be truncated.Or the word is a correct one(not truncated) or, if it is truncated it should not appear in the result.

    Can you help me?

    10q

  • sorry for the posts

    I was receiving this error : Sorry the application encountered an unexpected error. Information about this error has been logged. If you continue to receive this message please contact the board administrator.

    ....

  • select substring(@a,0,13)

  • try something like this:

    select left(@a, 15 - charindex(' ', reverse(@a)))

    _______________________________________________________________

    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/

  • This should work.. Spaces or not.

    SELECT

    SUBSTRING(LEFT(@A, 15), 0, LEN(LEFT(@A, 15)) - CHARINDEX(SPACE(1), REVERSE(LEFT(@A, 15))) + 1)

  • There is probably a more elegant solution, but this handles strings of 15 characters and such:DECLARE @Foo TABLE (Val VARCHAR(50))

    INSERT @Foo

    SELECT 'michael the greatest thing ever!!'

    UNION ALL SELECT 'Mike is radical'

    UNION ALL SELECT 'Mike is radical no?'

    UNION ALL SELECT 'WillThisStringEverEnd???'

    UNION ALL SELECT 'ThisHasASpaceInItBut Not Until The End'

    UNION ALL SELECT 'Small'

    SELECT

    CASE

    WHEN (CHARINDEX(SPACE(1), Val, 16) > 16)

    OR (LEN(Val) > 15 AND CHARINDEX(SPACE(1), Val, 16) = 0)

    THEN

    CASE

    -- need to break word

    WHEN CHARINDEX(SPACE(1), LEFT(Val, 15)) > 1 AND SUBSTRING(Val, 16, 1) SPACE(1)

    THEN LEFT(Val, 15 - CHARINDEX(SPACE(1), REVERSE(LEFT(Val, 15))))

    ELSE

    LEFT(Val, 15)

    END

    ELSE

    LEFT(Val, 15)

    END

    FROM @FooEDIT: Forgot to remove part of the CASE that was not needed and found an edge-case bug.

Viewing 6 posts - 1 through 5 (of 5 total)

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