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
i.e I want to update this new column
Thanks in advance...!
This question is related to
sql-server
split
sql-update
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
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)))
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
SELECT SUBSTRING(ParentBGBU,0,CHARINDEX('-',ParentBGBU,0)) FROM dbo.tblHCMMaster;
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.
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]%'
Please find the below query also split the string with delimeter.
Select Substring(@String1,0,CharIndex(@delimeter,@String1))
Source: Stackoverflow.com