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

Question

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.

Which RDBMS are you using? String manipulation function are largely platform specific. — Giorgos Betsos
– Giorgos Betsos, Commented Nov 29, 2017 at 9:55

Damien_The_Unbeliever · Accepted Answer · 2017-11-29 10:02:46Z

4

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

answered Nov 29, 2017 at 10:02

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

raymantle Over a year ago

Perfect. It's easy when you know how. Thanks.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related