I have a table with a phoneNumber column which Im trying to split into other columns

Here is some of the data I have from the phoneNumber field

0123456 899 x8787

0123456788 int 344

Internal 1234x67

012345 678

X6798

The output I require would look like this

Original                                  Newfield1                  Newfield2                  Newfield3      Newfield4

0123456 899 x8787             0123456 899             x                                  8787               null

0123456788 int 344             0123456788              int                                344                 null

Internal 1234x67                  Internal                      1234                           x                      67

012345 678                           012345                       678                             null                 null

X6798                                    X                                 6798                           null                 null

Thanks in advance

Sorry, I could not understand the logic. Could you please explain more?

Why the first result should not be as below?

Original                                  Newfield1                  Newfield2                  Newfield3      Newfield4

0123456 899 x8787             0123456                    899                             x                    8787

I have a table with a phoneNumber column which Im trying to split into other columns

Here is some of the data I have from the phoneNumber field

0123456 899 x8787

0123456788 int 344

Internal 1234x67

012345 678

X6798

The output I require would look like this

Original                                  Newfield1                  Newfield2                  Newfield3      Newfield4

0123456 899 x8787             0123456 899             x                                  8787               null

0123456788 int 344             0123456788              int                                344                 null

Internal 1234x67                  Internal                      1234                           x                      67

012345 678                           012345                       678                             null                 null

X6798                                    X                                 6798                           null                 null

Thanks in advance

Try something like this:

DECLARE @phone TABLE (num varchar(50))
INSERT INTO @phone (num) values ('0123456 899 x8787'), ('0123456788 int 344'), ('Internal 1234x67'), ('012345 678'), ('X6798 ')

SELECT 
	LEFT(REPLACE(LEFT(num, CASE WHEN CHARINDEX('x', num, 0) = 0 THEN LEN(num) ELSE CHARINDEX('x', num, 0) END), 'x', ''), CASE WHEN CHARINDEX('int', num, 0) = 0 THEN LEN(num) ELSE CHARINDEX('int', num, 0) -1 END) as Dial,
	CASE WHEN CHARINDEX('int', replace(num, 'internal', 'int'), 0) > 0 THEN REPLACE(REPLACE(SUBSTRING(replace(num, 'internal', 'int'), CHARINDEX('int', replace(num, 'internal', 'int'), 0), CASE WHEN CHARINDEX('x', REPLACE(num, 'internal', 'int'), 0) = 0 THEN LEN(REPLACE(num, 'internal', 'int')) ELSE CHARINDEX('x', REPLACE(num, 'internal', 'int'), 0) END), 'int ', ''), 'x', '') END as Internal,
	CASE WHEN CHARINDEX('x', num, 0) > 0 THEN replace(RIGHT(num, 1+ LEN(num) - CHARINDEX('x', num, 0)), 'x', '') END as Extention 
FROM @phone

Hi, many thanks for this solution which has helped quite a bit. Could it be extended to cover the possible values below. I got a bit mixed up when I tried it myself.  The rule for an extention is 5 numeric characters.

0123-4567891:10101

0123 4567891 Ext: 10101

0123 4567891 ext 10101

01234567891 ex10101

0123 4567891 main reception

10101 (Judy Ritchie)

0123 4567891 opt 1

) 1234567891 x 10101

0123 4567891 Extn 10101

123-98765

0123 4567891 / 10101

ext 10101

0123 4567891 - Team Secretary

Output for above

Dial                             Internal                                  Extention

01234567891                                                           10101

0123 4567891                                                          10101

0123 4567891                                                          10101

01234567891                                                           10101

0123 4567891

                                                                   10101

0123 4567891

01234567891                                                           10101

0123 4567891                                                          10101

                               12398765

0123 4567891                                                          10101

                                                                   10101

0123 4567891

• Edited by jez123456 11 minutes ago