SQL Substring for multiple select statement

Question

0.00/5 (No votes)

See more:

Hi I want to create a sub string for one of the fields called

clip_clip_title

normally the clip title is called PRT-12345_2015_06_10_12364.mov now i just want to cut the name so it reads

PRT-12345_2015_06_10

But not sure how substring would work in multi select statement like this:

select CLP_Clip_Name  + ' - ' + CLP_Clip_Title + '</a>'+ ' - ' + CLP_duration from Farmer_CLP_Clips C with (NOLOCK) inner join gl_Tasks_Portal TP on TP.TaskID = TaskID and TP.TaskData_Name = 'FLE_ID' and TP.TaskData_Value = C.FLE_ID order by C.CLP_clip_title

i know how to do the simple

SQL

select substring('clip_clip_title', 1,15);

which works but in multi select whats the best way to do this?
Thanks in advance

Posted 10-Jun-15 0:41am

Geofferz

Add a Solution

Comments

Geofferz 10-Jun-15 7:21am

so just to be clear i would like to return select CLP_Clip_Name + '_' + CLP_Clip_Title + CLP_Duration from....

so combining all 4 together to make one filename but i only want to bring back part of the CLP_Clip_title not full title. that make sense?

3 solutions

Solution 2

Simply replace

SQL

CLP_Clip_Title

with

SQL

substring('clip_clip_title', 1,15)

Posted 10-Jun-15 1:03am

Kornfeld Eliyahu Peter

Solution 3

It should be

select substring(clip_clip_title,1, 20) from Farmer_CLP_Clips

Reference: https://msdn.microsoft.com/en-us/library/ms187748.aspx[^]

Posted 10-Jun-15 1:06am

Peter Leow

Updated 10-Jun-15 1:07am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-06-10T01:01:00

If it's the same format each time, and you only want the first 15 characters of each name, then just replacing CLP_Clip_Title with SUBSTRING('clip_clip_title', 1,15) will do it.
If you want anything more complex (such as a variable length substring based on the location of the last "_" in the title) then define your own SQL function: https://technet.microsoft.com/en-us/library/aa214363(v=sql.80).aspx[^] and replace the SUBSTRING call with that.