Add a '-' in a varchar SQL

Question

I need to transform some strings from this format:

"1020202020"

To

"1-0-2-0-2-0-2-0-2-0"

How can I do that in a simple way?

Thanks

JC Ford · Accepted Answer · 2015-03-23T15:42:55.197

2

Every day I wake up and think to myself "What impossible thing will CTEs make possible today?"

;with cte as (
    select 
        '1020202020' inputstring,
        convert(varchar(max),'') outputstring
    union all
    select
        substring(inputstring,2,len(inputstring)),
        outputstring + left(inputstring,1) + '-' + case when len(inputstring) = 2 then right(inputstring,1) else '' end
    from cte
    where len(inputstring) > 1
)
select top 1 outputstring from cte order by len(outputstring) desc

edited Mar 23 '15 at 15:42

answered Mar 23 '15 at 15:35

JC Ford

6,946
3
25
34

Accepted 10 seconds after it was posted?? – Tim Schmelter Mar 23 '15 at 15:36
Made a small adjustment. Didn't need the 'X' padding the string. – JC Ford Mar 23 '15 at 15:43

score 0 · Answer 2 · answered Mar 23 '15 at 15:28

0

Your question is unclear on the exact rules are for the placement of the hyphens. So, there might be some clever method using replace().

For your example string:

select replace(replace(col, '02', '-0-2'), '20', '2-0')

answered Mar 23 '15 at 15:28

Gordon Linoff

1,242,037
58
646
786

Actually i thought it was simple as _put a hyphen between all characters_. I guess [this method](http://stackoverflow.com/a/6079259/284240) should work(with 1 as parameter) combined with [this](http://stackoverflow.com/a/6170551/284240). However, the last hyphen must be removed. – Tim Schmelter Mar 23 '15 at 15:32

score 0 · Answer 3 · answered Mar 23 '15 at 15:43

0

I would proceed like this :

TRIM(TRAILING "-" FROM REPLACE("10203040", "0", "-0-"))

answered Mar 23 '15 at 15:43

Federico

1,231
9
13

What makes you think there's anything special about the zeros? – SamB Mar 23 '15 at 20:02
1

well the question says: "... this format: 1020202020 ..." – Federico Mar 23 '15 at 22:48

score 0 · Answer 4 · answered Mar 23 '15 at 16:32

Here's a non-loop/non-recursive solution. I had some fun with REPLACE() and it's VERY efficient. It can process 100,000 rows in less than a second which is probably better than any looping or recursive solution.

IF OBJECT_ID('strings') IS NOT NULL
    DROP TABLE strings;

CREATE TABLE strings (string BIGINT);

--Populate the table
WHILE(SELECT COUNT(*) FROM strings) < 100000
BEGIN
    INSERT INTO strings
        SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL
        SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL
        SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL
        SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL
        SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000 UNION ALL SELECT RAND() * 10000000000
END

--Add the hyphens with REPLACE() and cut off the extra hyphen at the end with SUBSTRING()
SELECT SUBSTRING(
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(string, '0', '0-'),'1','1-'),'2','2-'),'3','3-'),'4','4-'),'5','5-'),'6','6-'),'7','7-'),'8','8-'),'9','9-'),
                0,
                LEN(string)*2
                ) AS String
FROM strings;

Abbreviated Results:

String
------------------------
5-6-1-2-9-4-9-0-8-1
7-5-8-5-1-9-3-0-9
2-2-4-7-6-1-5-7-6-9
9-3-4-6-1-0-3-5-0
9-9-5-3-6-8-8-7-8-9
5-3-7-2-0-5-3-0-6
8-4-0-8-9-8-9-4-5-0
9-8-6-4-6-8-4-4-3-9
6-6-5-8-2-8-7-3-9-2
...

Add a '-' in a varchar SQL

4 Answers4