0

I have a problem, if I have data like this :

1. 2|7000|0001|Werk 0001 Standort 0001|0
2. 16|701A|0101109|KANTOR-R. DAPUR LT.1|0
3. 401|701H|03PLNT2|PABRIK-GEDUNG PLANT LT.2|0 
4. 10272|B100|0109000|AUNG THEIK DI|161794|0

and I want to separate the first int like this :

1. 2
2. 16
3. 401
4. 10272

I already using a substring, but the problem is, the number of digits in the first int is not fixed, so I can't using it, so I want to ask how to separate using that first symbol from |, before I insert in database (separate in trigger)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
coba coba
  • 19
  • 3

2 Answers2

4

You can use charindex():

select convert(int, left(col, charindex('|', col) - 1))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Gordon's answer (+1) is probably the way to go here. But there is another option using STRING_SPLIT, if you are using SQL Server 2016 or later:

SELECT
    (SELECT TOP 1 value FROM STRING_SPLIT(col, '|')) AS first_part
FROM yourTable;

enter image description here

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360