CODEWITHSUNDEEP
sql-server
Willy
Willy

Reputation: 10650

Alternatives to SQL Server QUOTENAME command without the limitation of 128 characters. How to put a string between parantheses

Is there any SQL Server function to make the same as QUOTENAME but without the limitation of 128 characters?

Now I have a string that is more than 128 characters and below function fails:

DECLARE @myString varchar(max)='''01'',''02'',''03'',''04'',''05'',''06'',''07'',''08'',''09'',''10'',''11'',''12'',''13'',''14'',''15'',''16'',''17'',''18'',''19'',''20'',''21'',''22'',''23'',''24'',''25'',''26'',''27'',''28'',''29'',''30'',''31'',''32'',''33'',''34'',''35'',''36'',''37'',''38'',''39'',''40'',''41'',''42'',''43'',''44'',''45'',''46'',''47'',''48'',''49'',''50'',''51'',''52''';

QUOTENAME(@myString, '()')

Obviously QUOTENAME fails because @myString is more than 128 characters, in this case is 260 characters length.

I want to put the string between parantheses.

Upvotes: 1

Views: 1862

Answers (1)

Thom A
Thom A

Reputation: 95830

Use REPLACE and wrap the value with the delimiter characters. QUOTENAME(@SomeString,'''') and N'''' + REPLACE(@SomeString,'''','''''') + N'''' would be equivalent, for example.

For yours, it would be N'(' + REPLACE(@MyString,')','))') + N')'.

SELECT QUOTENAME('Hello(There)','(') AS Quotename,
       N'(' + REPLACE('Hello(There)',')','))') + N')' AS Replace;

Quotename       Replace
--------------- ---------------
(Hello(There))) (Hello(There)))

Notice that for characters that have both left and right characters (such as the parenthesis (()) here) you only need to escape the right character, not the left.

Upvotes: 2

Related Questions