select string before / - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select string before /

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2010-05-12 : 12:02:34

Hi i have a string in a sql sp, which i need to select everything before the first /

string example is 66666/232323/232323
i need 66666

any ideas how i can do this?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-12 : 12:05:59

Try this:

Select substring('66666/232323/232323',1, charindex('/','66666/232323/232323') -1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:06:18

[code]SELECT LEFT(field,CASE WHEN CHARINDEX('/',field)>0 THEN CHARINDEX('/',field)-1 ELSE LEN(field) END) FROM Table[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-12 : 12:29:39

quote:
Originally posted by pk_bohra

Try this:

Select substring('66666/232323/232323',1, charindex('/','66666/232323/232323') -1)

This will throw an error on a string that does not contain a /


DECLARE @foo VARCHAR(512)
SET @foo = '66666/232323/232323'

Select substring(@foo,1, charindex('/',@foo) -1)

SET @foo = 'asdasda'

Select substring(@foo,1, charindex('/',@foo) -1)

Results

Msg 537, Level 16, State 2, Line 8
Invalid length parameter passed to the LEFT or SUBSTRING function.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-12 : 12:33:39

Similar to visakh16:


SELECT CASE WHEN field LIKE '%/%' THEN LEFT(field, CHARINDEX('/', field) -1 ) ELSE field END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-12 : 12:36:41

quote:
Originally posted by Transact Charlie

quote:
Originally posted by pk_bohra

Try this:

Select substring('66666/232323/232323',1, charindex('/','66666/232323/232323') -1)

This will throw an error on a string that does not contain a /

DECLARE @foo VARCHAR(512)
SET @foo = '66666/232323/232323'

Select substring(@foo,1, charindex('/',@foo) -1)

SET @foo = 'asdasda'

Select substring(@foo,1, charindex('/',@foo) -1)

Results
Msg 537, Level 16, State 2, Line 8
Invalid length parameter passed to the LEFT or SUBSTRING function.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I noted this but by the time I wanted to correct it Visakh has already provided the solution and as usual the solution provided by him was better so i just have not edited my reply.

I am here to learn from Masters and help new bees in learning.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-12 : 20:38:32

New way;

declare @f table (f varchar(200) default '10666/616232/323232/323')
insert @f values(default)
insert @f select '2313139098453'

SELECT CASE WHEN PATINDEX('%/%', f) = 0 THEN f ELSE STUFF(f, PATINDEX('%[/]%', f), LEN(f)-LEN(PATINDEX('%[/]%', f)),'') END 
FROM @f

UenTsin
Starting Member

4 Posts

Posted - 2010-05-13 : 00:04:38

that's what i really wanna too, it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources