Adding of single quote in a string

Question

Adding of single quote in a string

skmoh2

SSChasing Mays

Points: 625
More actions
November 2, 2022 at 4:13 am

#4107583

I need to add a single quote in each of the alternate characters. The input string could be a variable length.
As an example:

Input String: 5,1,6,1,69,1
output: '5',1,'6',1,'69',1
Input String: 5,1,6,1,3,2,5,3,69,1
output: '5',1,'6',1,'3',2,'5',3,'69',1

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Collins SSCrazy Points: 2174 More actions · Answer 1

drop table if exists #some_table;
go
create table #some_table (
  input_string varchar(200) not null);

insert #some_table(input_string) values
('5,1,6,1,69,1'),
('5,1,6,1,3,2,5,3,69,1');

select string_agg(iif((ss.ordinal-1)%2=0, concat('''', ss.[value], ''''), ss.[value]), ',')
       within group (order by ss.ordinal) [output]
from #some_table st
     cross apply string_split(st.input_string, ',', 1) ss
group by st.input_string;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246986 More actions · Answer 2

The enable_ordinal argument for STRING_SPLIT is not implemented in 2019.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ed B Hall of Fame Points: 3669 More actions · Answer 3

Does this work? I don't know if there will ever be spaces, but I removed them anyway.

DECLARE @InputString VARCHAR(1000) = '5, 1,6, 1,69,1, 123456, 9'
SELECT REPLACE(CONCAT('''', REPLACE(@InputString, ',', ''','''), ''''), ' ', '')

In case of preceding or trailing commas.

DECLARE @InputString VARCHAR(1000) = '5,     1,6, 1,69,1, 123456, 9,'

SELECT REPLACE(CONCAT('''', REPLACE(TRIM(',' FROM @InputString), ',', ''','''), ''''), ' ', '')

Steve Collins SSCrazy Points: 2174 More actions · Answer 4

Phil Parkin wrote:

The enable_ordinal argument for STRING_SPLIT is not implemented in 2019.

It's true. It's available in Azure SQL compatibility level 150 (and above) and SQL Server 2022. There are alternatives such as:

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Mike01 SSChampion Points: 11395 More actions · Answer 5

How about a cte?

drop table if exists #some_table;
go
create table #some_table (
input_string varchar(200) not null);

insert #some_table(input_string) values
('5,1,6,1,69,1'),
('5,1,6,1,3,2,5,3,69,1'),
('5 ,2,36,1,69,1')
;


;with cte as (select input_string, case when RowNum %2 = 0 then [value] else Concat('''',[value],'''') end as NewValue from ( 
select input_string, ltrim(rtrim([value])) as [Value], row_number() over (partition by st.input_string order by st.input_string) as RowNum
from #some_table st
cross apply string_split(st.input_string, ',') ss) x)

SELECT input_string, 
STUFF( 
( SELECT ',' + NewValue 
FROM cte t2 
WHERE t1.input_string=t2.input_string 
FOR XML PATH('') 
), 
1, 
1,'' 
) AS NewValue 
FROM cte t1 
GROUP BY input_String

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

skmoh2 wrote:

I need to add a single quote in each of the alternate characters. The input string could be a variable length.
As an example:
Input String: 5,1,6,1,69,1
output: '5',1,'6',1,'69',1
Input String: 5,1,6,1,3,2,5,3,69,1
output: '5',1,'6',1,'3',2,'5',3,'69',1

If you explain why this strange requirement exists, I'll provide the exact code necessary to accomplish it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 7

Jeff Moden wrote:

skmoh2 wrote:
I need to add a single quote in each of the alternate characters. The input string could be a variable length.
As an example:
Input String: 5,1,6,1,69,1
output: '5',1,'6',1,'69',1
Input String: 5,1,6,1,3,2,5,3,69,1
output: '5',1,'6',1,'3',2,'5',3,'69',1
If you explain why this strange requirement exists, I'll provide the exact code necessary to accomplish it.

My guess is that when you put a single quote in front of a number in an Excel cell then the cell is formatted as text not a number.