Having issue with substring in SQL Server

Question

I have to split the resolution something like below:

declare @t table (resolution nvarchar(100))

Insert into @t values ('1366 X 768 @ 60 Hz'),
('1024 X 768 @ 60 Hz'),
('640 X 480 @ 70 Hz')

I want the output as

Fst   |  Snd  | Thd  
------+-------+-----------
1366  |  768  |  60 Hz  
1024  |  768  |  60 Hz  
640   |  480  |  70 HZ

I have tried this query

select 
    *,
    substring(resolution, 1, charindex('X', resolution) - 1) as fst,
    substring(resolution, charindex('X', resolution) + 1, charindex('@', resolution) + 1) as snd
from 
    @t

But it is not returning the correct output.

Can anyone please show me what is wrong in here?

The third argument to substring is the length, not the end of the substring to be returned. charindex('@', resolution) + 1 is not the length of the vertical resolution ('768'). — HABO
– HABO, Commented Sep 7, 2019 at 18:32

mkRabbani · Accepted Answer · 2019-09-07 10:34:51Z

4

You can also try PARSENAME() function if there are only 3 part every time, and data format always as you shown.

SELECT 
PARSENAME(REPLACE(REPLACE(resolution,' X ','.'),' @ ','.') ,3) Fst,
PARSENAME(REPLACE(REPLACE(resolution,' X ','.'),' @ ','.') ,2) Snd,
PARSENAME(REPLACE(REPLACE(resolution,' X ','.'),' @ ','.') ,1) Thd
FROM @t

Output is-

Fst     Snd     Thd
1366    768     60 Hz
1024    768     60 Hz
640     480     70 Hz

If you wants to exlude ' Hz' from the Thd column, Just do it-

SELECT 
PARSENAME(REPLACE(REPLACE(REPLACE(resolution,' X ','.'),' @ ','.'),' Hz','') ,3) Fst,
PARSENAME(REPLACE(REPLACE(REPLACE(resolution,' X ','.'),' @ ','.'),' Hz','')  ,2) Snd,
PARSENAME(REPLACE(REPLACE(REPLACE(resolution,' X ','.'),' @ ','.'),' Hz','')  ,1) Thd
FROM @t

edited Sep 7, 2019 at 10:34

answered Sep 7, 2019 at 10:25

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ilyes Over a year ago

That will return 'Hz' in the third column. Rethink and edit your answer.

mkRabbani Over a year ago

@Sami nope, you can check the output I just added.

Ilyes Over a year ago

My bad didn't notice the desired results include 'Hz' in the OP Q

mkRabbani Over a year ago

Whats the issue? Hz is OP's requirement. Whats problem here?

Ilyes · Accepted Answer · 2019-09-07 11:31:04Z

Here you go

declare @t table (resolution nvarchar(100))

Insert into @t values ('1366 X 768 @ 60 Hz'),
('1024 X 768 @ 60 Hz'),
('640 X 480 @ 70 Hz');

SELECT LEFT(Resolution, CHARINDEX('X', Resolution) - 1) [1St],
       SUBSTRING(Resolution, CHARINDEX('X', Resolution)+1, CHARINDEX('@', Resolution)-
       CHARINDEX('X', Resolution)-2) [2nd],
       SUBSTRING(Resolution, CHARINDEX('@', Resolution)+1, CHARINDEX('H', Resolution)-
       CHARINDEX('@', Resolution)-2) [3rd]
FROM @t;

Online demo

Or more better(without white spaces)

SELECT LEFT(Resolution, CHARINDEX('X', Resolution) - 2) [1St],
       SUBSTRING(Resolution, 
                 CHARINDEX('X', Resolution)+2, 
                 CHARINDEX('@', Resolution)- CHARINDEX('X', Resolution)-3
                ) [2nd],
       SUBSTRING(Resolution, 
                 CHARINDEX('@', Resolution)+2, 
                 CHARINDEX('H', Resolution) - CHARINDEX('@', Resolution)-3
                ) [3rd]
FROM @t

If you want 'Hz' to be in the third column then

SELECT LEFT(Resolution, CHARINDEX('X', Resolution) - 2) [1St],
       SUBSTRING(Resolution, 
                 CHARINDEX('X', Resolution)+2, 
                 CHARINDEX('@', Resolution)- CHARINDEX('X', Resolution)-3
                ) [2nd],
       SUBSTRING(Resolution, 
                 CHARINDEX('@', Resolution)+2, 
                 LEN(Resolution)
                ) [3rd]
FROM @t;

Also, you could use PARSENMAE() function (as mkrabbani provide)to return both results as the following:

With 'Hz'

SELECT Resolution,
       PARSENAME(Res, 3) Fst,
       PARSENAME(Res, 2) Snd,
       PARSENAME(Res, 1) Thd
FROM
(
  VALUES
  ('1366 X 768 @ 60 Hz'),
  ('1024 X 768 @ 60 Hz'),
  ('640 X 480 @ 70 Hz')
) T(Resolution) 
CROSS APPLY 
(
  SELECT REPLACE(REPLACE(T.Resolution, ' X ', '.'), ' @ ', '.') Res
) TT;

Without 'Hz':

SELECT Resolution,
       PARSENAME(Res, 3) Fst,
       PARSENAME(Res, 2) Snd,
       PARSENAME(Res, 1) Thd
FROM
(
  VALUES
  ('1366 X 768 @ 60 Hz'),
  ('1024 X 768 @ 60 Hz'),
  ('640 X 480 @ 70 Hz')
) T(Resolution) 
CROSS APPLY 
(
  SELECT REPLACE(REPLACE(REPLACE(T.Resolution, ' X ', '.'), ' @ ', '.'), 'Hz', '') Res
) TT;

Demo for the last 3 queries.

Sabir Al Fateh · Accepted Answer · 2019-09-07 11:17:17Z

2

You can use PARSENAME function to split :

SELECT *,
    PARSENAME(REPLACE(PARSENAME(REPLACE(resolution,'@','.'),2),'X','.'),2) 'Fst' ,
    PARSENAME(REPLACE(PARSENAME(REPLACE(resolution,'@','.'),2),'X','.'),1) 'Snd' ,
    PARSENAME(REPLACE(resolution,'@','.'),1) 'Thd'
FROM @t

answered Sep 7, 2019 at 11:17

Sabir Al Fateh

1,8033 gold badges21 silver badges27 bronze badges

Comments

nimajv · Accepted Answer · 2019-09-07 10:32:15Z

1

You can use this :

select 
    *,
    substring(resolution, 1, charindex('X', resolution) - 1) as fst,
    substring(resolution, charindex('X', resolution) + 1, charindex('@', resolution) - charindex('X', resolution)-1) as snd ,
    substring(resolution, charindex('@', resolution) + 1,len(resolution) -  charindex('@', resolution)  ) as thr
from 
    @t

answered Sep 7, 2019 at 10:32

nimajv

4533 silver badges12 bronze badges

Comments

Serg · Accepted Answer · 2019-09-07 10:47:29Z

1

A bit cleaner code using APPLY

SELECT LEFT(Resolution, idx1 - 2) [1St],
       SUBSTRING(Resolution, idx1 + 2, idx2 - idx1 - 3) [2nd],
       SUBSTRING(Resolution, idx2 + 2, CHARINDEX('H', Resolution) - idx2 - 3) [3rd]
FROM @t
CROSS APPLY (
     SELECT CHARINDEX('X', Resolution) idx1
          , CHARINDEX('@', Resolution) idx2) i

answered Sep 7, 2019 at 10:47

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Comments

Zohar Peled · Accepted Answer · 2019-09-08 08:07:41Z

1

And since no string parsing post would be complete without an XML based solution:

SELECT ResXml.value(N'/x[1]', 'nvarchar(100)') As Fst,  
       ResXml.value(N'/x[2]', 'nvarchar(100)') As Snd, 
       ResXml.value(N'/x[3]', 'nvarchar(100)') As Thd
FROM
(
    SELECT CAST('<x>'+ REPLACE(REPLACE(resolution, ' X ', '</x><x>'), ' @ ', '</x><x>') + '</x>' As Xml) As ResXml
    FROM @t
) As x

answered Sep 8, 2019 at 8:07

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Collectives™ on Stack Overflow

Having issue with substring in SQL Server

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related