CODEWITHSUNDEEP
sqlstringsql-server-2012
raymantle
raymantle

Reputation: 121

How can I add a character into a specified position into string in SQL?

I have a column which can contain up to 500 characters (Message Varchar (500)).

I need to insert a Caret (^) at position 250, or if there are less then 250 characters i.e. 120, then at the last characters position.

Example 497 characters;

strong textFar far away, behind the word mountains, far from the countries Vokalia and Consonantia, there live the blind texts. Separated they live in Bookmarksgrove right at the coast of the Semantics, a large language ocean. A small river named Duden flows b^y their place and supplies it with the necessary regelialia. It is a paradisematic country, in which roasted parts of sentences fly into your mouth. Even the all-powerful Pointing has no control about the blind texts it is an almost unorthographi.

The Caret is inserted at characters 250 which splits the word "by" into "b^y".

Example 140 characters;

Far far away, behind the word mountains, far from the countries Vokalia and Consonantia, there live the blind texts. Separated they live in.^

As this example has less than 250 characters, then the Caret is inserted at the end of the message.

The max characters that will ever be inputted will be 499, therefore leaving 1 character for the inserting of the Caret.

Any help would be grateful.

Upvotes: 0

Views: 145

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Quite simple with CASE and STUFF:

-- @t is assumed to contain the text to be changed

CASE WHEN LEN(@t) > 250
    THEN STUFF(@t,250,0,'^')
    ELSE @t + '^' END

Upvotes: 4

Related Questions