Extract text from a column

Question

I've got a column 'Title' and it holds values similar to this text here

HotelBeds SLU: Refugi Dels Isards Hotel in Pas de la Casa, Andorra (4 nights)

I'd want to select anything that comes after the colon (:) and exclude anything after the comma.

I tried this:

Select SUBSTRING(title,
            CHARINDEX(':',title)+1,
          CHARINDEX(',',title)
          -CHARINDEX(':',title)-1) HotelName from booking_tbl

I see this error

Invalid length parameter passed to the LEFT or SUBSTRING function.

What am I doing wrong?

Its exactly as the error says? The values you are passing to sub-string are wrong. Select the charindex values without using the sub-string and you'll soon work out what you are doing wrong. — Dale K
– Dale K, Commented Feb 26, 2020 at 6:45

Suraj Kumar · Accepted Answer · 2020-02-26 07:03:07Z

3

You can try this using 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 ] )

Implementation

DECLARE @x varchar(max) = 'HotelBeds SLU: Refugi Dels Isards Hotel in Pas de la Casa, Andorra (4 nights)'
SELECT
  RTRIM(LEFT( @x , CHARINDEX(':',  @x ))) AS PartA,
  SUBSTRING( @x , CHARINDEX(':',  @x ) + 1, 8000) AS PartB

db<>fiddle Demo

edited Feb 26, 2020 at 7:03

answered Feb 26, 2020 at 6:47

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

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

Comments

Yogesh Sharma · Accepted Answer · 2020-02-26 10:22:53Z

1

You need to append the , or : as becuase some titles don't have , or : :

SELECT SUBSTRING(title, CHARINDEX(':',title + ':') + 1, 
                 CHARINDEX(',', title + ',') - CHARINDEX(':',title + ':') 
                ) HotelName 
FROM booking_tbl bt;

You can either add where clause to avoid error :

WHERE CHARINDEX(',', tittle) > 0 AND
      CHARINDEX(':', title) > 0;

edited Feb 26, 2020 at 10:22

answered Feb 26, 2020 at 10:09

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

yemo · Accepted Answer · 2020-02-26 13:43:52Z

1

declare @str nvarchar(250)
set @str = 'HotelBeds SLU: Refugi Dels Isards Hotel in Pas de la Casa, Andorra (4nights)';
select ltrim(
 substring(
  @str, 
  charindex(':', @str) + 1, 
  charindex(',', @str) - (charindex(':', @str) + 1)
 )
)

edited Feb 26, 2020 at 13:43

answered Feb 26, 2020 at 13:32

yemo

1558 bronze badges

Collectives™ on Stack Overflow

Extract text from a column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related