How to split a string after specific character in SQL Server and update this value to specific column

Question

I have table with data 1/1 to 1/20 in one column. I want the value 1 to 20 i.e value after '/'(front slash) is updated into other column in same table in SQL Server.

Example:

Column has value 1/1,1/2,1/3...1/20
new Column value 1,2,3,..20

That is, I want to update this new column.

Lamak · Accepted Answer · 2018-07-03T16:26:52.603

117

Try this:

UPDATE YourTable
SET Col2 = RIGHT(Col1,LEN(Col1)-CHARINDEX('/',Col1))

edited Jul 03 '18 at 16:26

answered Feb 13 '12 at 12:12

Lamak

69,480
12
108
116

1

great idea to combine `charindex` with `right` - it also works with `left`, really cool :) – Stefan Brendle Nov 05 '13 at 15:40
1

Just needed myself, and it works in DB2 too if you sub `LEN` with `LENGTH` and `CHARINDEX` with `LOCATE` – Sean Branchaw Aug 31 '16 at 17:36
It helped finding number in string after space `@declare s varchar(10) , REPLACE(RIGHT(string,LEN(@)-CHARINDEX(' ',@s)),' ','')` – Tejas Oct 26 '18 at 12:34

score 14 · Answer 2 · edited Jun 01 '16 at 05:09

14

Please find the below query also split the string with delimeter.

Select Substring(@String1,0,CharIndex(@delimeter,@String1))

edited Jun 01 '16 at 05:09

Pedram

6,256
10
65
87

answered Jul 24 '15 at 06:52

user5151121

141
1
2

score 2 · Answer 3 · edited Oct 24 '14 at 12:50

2

From: http://www.sql-server-helper.com/error-messages/msg-536.aspx

To use function LEFT if not all data is in the form '1/12' you need this in the second line above:

Set Col2 = LEFT(Col1, ISNULL(NULLIF(CHARINDEX('/', Col1) - 1, -1), LEN(Col1)))

edited Oct 24 '14 at 12:50

Soner Gönül

97,193
102
206
364

answered Oct 24 '14 at 12:30

Miguel

81
1
8

I guess some one didn't understand what I meant. Your data: 1/1; 1/2; 2/1; 2/2; 2/3; 3; 4/1; You might have an error in "3" due to the missing sub-part. – Miguel Feb 12 '15 at 15:19

score 2 · Answer 4 · edited Jan 04 '17 at 12:22

2

SELECT SUBSTRING(ParentBGBU,0,CHARINDEX('-',ParentBGBU,0)) FROM dbo.tblHCMMaster;

edited Jan 04 '17 at 12:22

FelixSFD

6,052
10
43
117

answered Jan 04 '17 at 12:21

Ankur Shah

467
5
3

may be not usefull for this question but this answer save my 1 hrs of googling. its giving me my expected result as `A5/LS/CHMB/17-18/WAI` will be `A5` – Imran Ali Khan Jan 17 '18 at 10:23
thanx.i got my solution using this – Aug 02 '19 at 07:15

score 2 · Answer 5 · answered Jul 04 '17 at 12:41

2

I know this question is specific to sql server, but I'm using postgresql and came across this question, so for anybody else in a similar situation, there is the split_part(string text, delimiter text, field int) function.

answered Jul 04 '17 at 12:41

mbarkhau

8,190
4
30
34

score 1 · Answer 6 · answered Feb 13 '12 at 12:20

Maybe something like this:

First some test data:

DECLARE @tbl TABLE(Column1 VARCHAR(100))

INSERT INTO @tbl
SELECT '1/1' UNION ALL
SELECT '1/20' UNION ALL
SELECT '1/2'

Then like this:

SELECT
    SUBSTRING(tbl.Column1,CHARINDEX('/',tbl.Column1)+1,LEN(tbl.Column1))
FROM
    @tbl AS tbl

score -1 · Answer 7 · edited Sep 10 '13 at 01:09

-1

SELECT emp.LoginID, emp.JobTitle, emp.BirthDate, emp.ModifiedDate  , 
      CASE  WHEN emp.JobTitle  NOT LIKE '%Document Control%'  THEN emp.JobTitle
            ELSE SUBSTRING(emp.JobTitle,CHARINDEX('Document Control',emp.JobTitle),LEN('Document Control'))
      END 
      ,emp.gender,emp.MaritalStatus
FROM   HumanResources.Employee [emp]
WHERE  JobTitle LIKE '[C-F]%'

edited Sep 10 '13 at 01:09

Taryn

242,637
56
362
405

answered Sep 10 '13 at 01:06

Siddhesh

1

Code-only post are discourage here. Perhaps you should include an explanation. – Mark Sep 10 '13 at 01:25
no explaination? and use of unrelated column names! – SHEKHAR SHETE Apr 16 '14 at 11:42

score -1 · Answer 8 · edited Apr 16 '14 at 08:32

Use CHARINDEX. Perhaps make user function. If you use this split often.
I would create this function:

CREATE FUNCTION [dbo].[Split]
(
    @String VARCHAR(max),
    @Delimiter varCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'INT_COLUMN' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'STRING_COLUMN' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

Please elaborate. Your answer is vague. – mzedeler Apr 16 '14 at 07:51 — mzedeler, Apr 16 '14 at 07:51

How to split a string after specific character in SQL Server and update this value to specific column

8 Answers8

Linked

Related