CODEWITHSUNDEEP
sqlsql-serversubstringpartial
lika
lika

Reputation: 21

How to extract part of string in SQL before first of the repaeting characters?

I have in SQl rows with following mixed values.

322600;323800;325000;326200;327400;329200
0;0;0;0;0;0
1184168;1188182;1192196;1196210;1200224;1204238
0;0;0;0;0;0
0;0;0;0;0;0

I would need to obtain only values before the first semicolon i.e. 

322600
0
1184168
0
0

I have tried:

,LEFT ([UtilizeProperties],CHARINDEX(';',[CalcTotGrossIncome])-1)

but I get only 3 first digits and 

,SUBSTRING([UtilizeProperties],CHARINDEX(';',[UtilizeProperties])-1,LEN([UtilizeProperties])-CHARINDEX(';',[UtilizeProperties]))

make my first values to 0 and 

,SUBSTRING([UtilizeProperties],CHARINDEX(';',[UtilizeProperties])+1,LEN([UtilizeProperties]))

takes them away totaly.

Thankful for kind advice.

I use SQL Service Management Studio

Upvotes: 0

Views: 473

Answers (2)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the following query CHARINDEX (Transact-SQL).

This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

Syntax

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

To get all the values you need to replace UNION with UNION ALL. You can add order by clause in final query for sorting the values.

with cte (SampleData) as
(
select '322600;323800;325000;326200;327400;329200'
union
select '0;0;0;0;0;0'
union
select '1184168;1188182;1192196;1196210;1200224;1204238'
union
select '0;0;0;0;0;0'
union
select '0;0;0;0;0;0'
)
SELECT 
        CAST(LEFT(SampleData, CHARINDEX(';', SampleData)-1) AS INT) OutputValue
From cte

Here is the db<>fiddle demo.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272056

Your first approach is correct except you are using different columns in LEFT and CHARINDEX. Change like so:

LEFT(UtilizeProperties, NULLIF(CHARINDEX(';', UtilizeProperties), 0) - 1)

Upvotes: 1

Related Questions