TSQL Question:
I have a string value in a column displayed like so:
Row ID, Name, Column1 1, Bob, |Gender - Male| 2, Sally, |Gender - Female| |Age - 30| 3, John, |Gender - Male| 4, Thomas, |Gender - Male| 5, Lewis, |Gender - Male| |Age - 20|
I only want to extract the value from the Column1 if there is only one group of ||, so for example in another column, column2, I have done
Replace(substring(column1,charindex('=',column1) 2,charindex('|',column1) ),'|','') AS column2
this gives me the values if there is a single set of |, but how do i ignore the 2 sets of pipes as if there is 2 sets of pipes then it extracts the first one and also don't extract the 2nd set of pipes. I want to be able to ignore and leave the value in the column if there are 2 sets of pipes and only update the ones that have 1 set of pipes.
So the above example dataset should look like this afterwards:
Row ID, Name, Column1 1, Bob, Male 2, Sally, |Gender - Female| |Age - 30| 3, John, Male 4, Thomas, Male 5, Lewis, |Gender - Male| |Age - 20|
I was thinking maybe can somehow scan the column 1 string value, if there is more than 1 -(dash) symbol then ignore?
or is there a better way?
CodePudding user response:
As others mention, your schema design should be redesigned into a normalized structure. As for your example of column2, not sure what you are trying to do there as it is looking for an "=" sign.
SQL Substring with CASE WHEN
Here's a script that returns your expected output:
DROP TABLE IF EXISTS #Temp
CREATE TABLE #Temp
(
RowID INT
,[Name] Varchar(100)
,Column1 VARCHAR(1000)
)
INSERT INTO #Temp
VALUES
(1,'Bob','|Gender - Male|')
,(2,'Sally','|Gender - Female| |Age - 30|')
,(3,'John','|Gender - Male|')
,(4,'Thomas','|Gender - Male|')
,(5,'Lewis','|Gender - Male| |Age - 20|')
SELECT *
,ParsedColumn = CASE
/*Only parse if exactly 2 pipes in data*/
WHEN NumOfPipes = 2 THEN SUBSTRING(Column1,B.IdxFirstHyphen 2,C.IdxFirstPipeAfterFirstDash-B.IdxFirstHyphen - 2)
/*Else return raw data from column1*/
ELSE Column1
END
FROM #Temp
CROSS APPLY (
SELECT NumOfPipes = LEN(Column1) - LEN(REPLACE(Column1,'|',''))
,IdxFirstHyphen = CHARINDEX('-',Column1) /*Location of first hyphen*/
) AS B
CROSS APPLY (SELECT IdxFirstPipeAfterFirstDash = CHARINDEX('|',Column1,B.IdxFirstHyphen)) AS C /*Location of first pipe after the first hyphen*/
CodePudding user response:
This is highly inefficient but works.
create function dbo.stringCounter (@Column nvarchar(255), @Character nchar(1))
returns int
AS
BEGIN
Declare @count int
select @Count=len(@Column) - len(replace(@Column,@Character,''))
Return @count
END
GO
select dbo.stringCounter(column1,'|'),column1 from TableName where dbo.stringCounter(column1,'|')=2
CodePudding user response:
This should be pretty efficient, first split the string on -
and extract the value with |
suffixed. Then aggregate and return only extracted values where column1 was only split into two rows:
select rowId, name, Iif(Count(*)=2,Max(Extracted),column1) Column1
from t
outer apply (
select case when Right(value,1)='|' then Trim(Replace(value,'|','')) end Extracted
from String_Split(column1, '-')
)x
group by rowId, name, column1;