How to remove Companyid and first underscore form filepath in select query itslef ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

hi i have following values in my table "sharefile"

SQL

FilePath               Users   CompanyId

1043_CAB1\111.txt	   2083	  1043
1043_CabFW\111.txt	   2083	  1043
1040_Admin\Admin.docx  2089	  1040

I need to select all rows same like above,but need to remove companyid with "_" in select query
my need is

SQL

FilePath           Users   CompanyId

CAB1\111.txt	   2083	  1043
CabFW\111.txt	   2083	  1043
Admin\Admin.docx   2089	  1040

I am try with replace function in select query,i can remove only Companyid form filepath,still need to remove "_" underscore also

This is query i tried

SQL

SELECT replace (FilePath,CompanyId ,'') as FilePath , Users, CompanyId FROM ShareFile

and result is

SQL

_CAB1\111.txt	2083	1043
_CabFW\111.txt	2083	1043
_Admin\Admin.docx	2089	1040

I am try to add "_" in replace function like this,but cant add,bcz of comapnyid datatype is int.Only in string datatype can add "_"

SQL

SELECT replace (FilePath,CompanyId & N'_' ,'') as FilePath , Users, CompanyId FROM ShareFile

Or if possible in sql select query can remove letters upto first "_" using substring function
Pls reply me how to achieve this

Regards
Araindb

Posted 16-Sep-15 20:17pm

Aravindba

Updated 16-Sep-15 20:18pm

v2

Add a Solution

2 solutions

Solution 1

2 possible solution:
-Use a nested replace and in the second try to remove underscore only (but I think that there is a character matching issue, caused by encoding)
-Or use simply a substring calculating len(companyId)+1

Posted 16-Sep-15 20:34pm

FrankNight

Comments

Aravindba 17-Sep-15 3:08am

Thank u for ur reply,i solve by myslef ,first i convert companyid as string and i add underscore inside replace function,pls see my solution

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)

Aravindba · Accepted Answer · 2015-09-16T21:10:00

Solution 2

Hi, i solve like this

SQL

SELECT  Replace (FilePath,CONVERT(varchar(10), CompanyId ) + N'_','') as FilePath, Users, CompanyId from ShareFile

After converting companyid to string,then i can append "_" and use in replace function.

Regards
Aravind

Posted 16-Sep-15 21:10pm

Aravindba