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?
 
EvilPostItCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
QlemoBatchelor, 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.