CODEWITHSUNDEEP
sqlsql-server
user2998990
user2998990

Reputation: 980

Get all characters of a string to the left of delimiter in sql

Following are the queries I am running to fetch all the characters which left to the @ delimiter. I am not able to get the proper results. The number of characters to the left of delimiter are unknown.

select @class = SUBSTRING('25@class1', CHARINDEX('@','25@class1')-2, 8000)


select @class = left('25@class1',LEN('25@class1')-CHARINDEX('@','25@class1'))

Please help

Upvotes: 1

Views: 14310

Answers (3)

Aruna
Aruna

Reputation: 12022

Method 1: (using SUBSTRING)

DECLARE @class VARCHAR(4000)
SELECT @class = SUBSTRING('25@class1', 1, CHARINDEX('@','25@class1') - 1)

Method 2: (using LEFT)

DECLARE @class VARCHAR(4000)
SELECT @class = LEFT('25@class1', CHARINDEX('@','25@class1') - 1)

Method 3: (using STUFF)

DECLARE @class VARCHAR(4000)
SELECT @class = STUFF('25@class1', CHARINDEX('@','25@class1'), (LEN('25@class1') - CHARINDEX('@','25@class1') + 1), '')

Upvotes: 5

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

I assume you want to display before @. And also provided logic to find after and before @ 

declare @class varchar(200)
select @class = left('25@class1', CHARINDEX('@','25@class1')-1)

select @class  --before @

select @class = right('25@class1',len('25@class1')- CHARINDEX('@','25@class1'))
select @class -- after @

Upvotes: 2

Mansoor
Mansoor

Reputation: 4192

DECLARE @class VARCHAR(100) = '25@class1'

SELECT SUBSTRING(@class,0,CHARINDEX('@',@class)) SELECT LEN(SUBSTRING(@class,0,CHARINDEX('@',@class)))

Upvotes: 0

Related Questions