T-SQL expression - removing sting after spefified charactor

Hello,
I have string value as follow.

50242/1

I would like to know the expression to remove after /
so string will be just

50242

However, I can't use string length for this because some time I will have
 
4433/1

4 digits instead of 5 digits.

Is there an expression to remove everything after specified letter? (in this case / )

I'm using SQL server 2008 express.


Thank you
jtuttle99Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
EvilPostItConnect With a Mentor Commented:
This should do the trick.

SELECT CASE CHARINDEX('/',YourField)
WHEN 0 THEN YourField
ELSE LEFT(YourField,(CHARINDEX('/',YourField)-1)
END AS 'FieldName'
FROM YOURTABLE

Open in new window

0
 
MINDSUPERBCommented:
If you there's only one character after /, then you can use the Rigth Function e.g. Right(Field,2).

See the link below:

http://msdn.microsoft.com/en-us/library/ms177532.aspx

Sincerely,
Ed
0
 
Alpesh PatelAssistant ConsultantCommented:
Hi review below ex.

Select Left('123/5',CHARINDEX('/','123/5')-1)

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
MINDSUPERBCommented:
I am sorry. My fault. I misinterpreted the question. You may try this one instead:

Left(YourField,Len(YourField)-2)

Ed
0
 
EvilPostItCommented:
This should do the trick.

SELECT REPLACE('12345/1','/','')

Open in new window

0
 
EvilPostItCommented:
Sorry mis-read the question.

If i had read it correctly i would have done something much the same as PatelAlpesh.
0
 
jtuttle99Author Commented:
I'm so sorry to tell you this but I can't use any length for this.
That's because sometimes I have string like

54165

so, if string includes /1, I want to get rid of the rest of it.

I.E.
string = convert to

54165/1    = 54165
54165/5    = 54165
54165       = 54165
5416         = 5416


Thank you

0
 
MINDSUPERBCommented:
Try this

LEFT(YourField, CHARINDEX('/', YourField) -1)

Ed

0
 
MINDSUPERBCommented:
Or you may use the IF Syntax as illustrated below:

IF CHARINDEX('/', YourField)>0
BEGIN
    SELECT LEFT(YourField, CHARINDEX('/', YourField) -1)
    FROM SomeTable
END

ELSE
BEGIN
    SELECT YourField    
FROM SomeOtherTable
END

Sincerely,
Ed
0
 
jtuttle99Author Commented:
Your Query worked in SELECT statement.
Is it possible to use it in Insert command? It's giving me error...


INSERT INTO mytable myfield Values CASE CHARINDEX('/', :job_number) WHEN 0 THEN :job_number ELSE LEFT(:job_number, CHARINDEX('/', :job_number) - 1) END AS job_number
0
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Almost:
INSERT INTO mytable (myfield)
Values (CASE CHARINDEX('/', :job_number) WHEN 0 THEN :job_number ELSE LEFT(:job_number, CHARINDEX('/', :job_number) - 1) END)
0
 
jtuttle99Author Commented:
I'm sorry that I didn't reply. I needed to work on something else...
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.