CODEWITHSUNDEEP
sqlsql-server
priyesh a
priyesh a

Reputation: 21

Adding hyphen in a column in sql table

I need help understanding how to add a hyphen to a column where the values are as follows, 8601881, 9700800,2170

The hyphen is supposed to be just before the last digit. There are multiple such values in the column and the length of numbers could be 5,6 or more but the hyphen has to be before the last digit.

Any help is greatly appreciated.

The expected output should be as follows, 860188-1,970080-0,217-0

Upvotes: 0

Views: 11989

Answers (4)

Jafar Sadik
Jafar Sadik

Reputation: 113

If you have "dash/hyphen" in your data, and you have to store it in varchar or nvarchar just append N before the data. For example: insert into users(id,studentId) VALUES (6,N'12345-1001-67890');

Upvotes: 0

fiveobjects
fiveobjects

Reputation: 4279

select concat(substring(value, 1, len(value)-1), '-', substring(value, len(value), 1)) from data;create table data(value varchar(100));

Here is the full example:

create table data(value varchar(100));
insert into data values('6789567');
insert into data values('98765434');
insert into data values('1234567');
insert into data values('876545');
insert into data values('342365');

select concat(substring(value, 1, len(value)-1), '-', substring(value, len(value), 1)) from data;


 | (No column name) |
 | :--------------- |
 | 678956-7         |
 | 9876543-4        |
 | 123456-7         |
 | 87654-5          |
 | 34236-5          |

In case OP meant there can be multiple numbers in the column value here is the solution:

create table data1(value varchar(100));
insert into data1 values('6789567,5467474,846364');
insert into data1 values('98765434,6474644,76866,68696');
insert into data1 values('1234567,35637373');


select t.value, string_agg(concat(substring(token.value, 1, len(token.value)-1), '-', 
substring(token.value, len(token.value), 1)), ',') as result 
from data1 t cross apply string_split(value, ',') as token group by t.value;


value                        | result                          
:--------------------------- | :-------------------------------
1234567,35637373             | 123456-7,3563737-3              
6789567,5467474,846364       | 678956-7,546747-4,84636-4        
98765434,6474644,76866,68696 | 9876543-4,647464-4,7686-6,6869-6

Upvotes: 3

mkRabbani
mkRabbani

Reputation: 16908

You can use LEN and LEFT/RIGHT method to get your desired output. Logic are given below:

Note: this will work for any length's value.

DECLARE @T VARCHAR(MAX) = '8601881'

SELECT LEFT(@T,LEN(@T)-1)+'-'+RIGHT(@T,1)

Upvotes: 0

Wes H
Wes H

Reputation: 4439

Using SQL SERVER 2017, you can leverage STRING_SPLIT, STUFF, & STRING_AGG to handle this fairly easily.

DECLARE @T TABLE (val VARCHAR(100)) ;

INSERT INTO @T (val) VALUES ('8601881,9700800,2170') ;

SELECT  t.val,
        STRING_AGG(STUFF(ss.value, LEN(ss.value), 0, '-'), ',') AS Parsed
FROM  @T AS t
  CROSS APPLY STRING_SPLIT(t.val, ',') AS ss
GROUP BY t.val ;

Returns

8601881,9700800,2170    => 860188-1,970080-0,217-0

STRING_SPLIT breaks them into individual values, STUFF inserts the hyphen into each individual value, STRING_AGG combines them back into a single row per original value.

Upvotes: 1

Related Questions