sql string values and only extract the first value from section of pipes

user_17448697 0
Viewed 117

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?

tsql sql split
3 Answers

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*/
user_46419280

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;

Demo Fiddle

user_153326500

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
user_36543490
Related