July 9, 2009 at 3:18 am
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
July 9, 2009 at 3:35 am
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.
....
July 9, 2009 at 11:59 am
select substring(@a,0,13)
July 9, 2009 at 12:23 pm
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/
July 9, 2009 at 2:19 pm
This should work.. Spaces or not.
SELECT
SUBSTRING(LEFT(@A, 15), 0, LEN(LEFT(@A, 15)) - CHARINDEX(SPACE(1), REVERSE(LEFT(@A, 15))) + 1)
July 10, 2009 at 3:41 pm
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