So this is the data I am pulling in from powershell (there's actually more, but it all follows the same pattern):

Name : SULESRKMA 1
Location : Leisure Services - Technology Services 2
DriverName : KONICA MINOLTA mc4695MF PS PPD 3
Shared : False 4
ShareName : 5
JobCountSinceLastReset : 0 6

I am trying to remove 'Name : ' and 'Location : ', and so on, but using the REPLACE command here is part of my sql query:

SELECT * FROM #ReadCmd
WHERE Result LIKE 'Name %:%'
INSERT INTO #output(Name)
SELECT REPLACE(Result, '% %: %', '')
From #ReadCmd
WHERE Result LIKE 'Name %:%'
SELECT * FROM #output

Or for example, here:

IF OBJECT_ID('tempdb..#fields') != 0 DROP TABLE #fields
CREATE TABLE #fields (Fields varchar(256))
INSERT INTO #fields (Fields)
SELECT REPLACE(Result, ' %: %', '')
FROM #ReadCmd
Where Result Like '% %: %'

The point is, I'd like to replace the '_________ : ' with nothing, but the REPLACE command reads the sql wild card '%' as an actual percent sign. Is there another way to accomplish this?

Using Select RIGHT(Result,CHARINDEX(': ',Result) -1) outputs in seperate cells:

 : SULESRKMA : PrimoPDF
oft XPS Document Writer : Fax : CutePDF Writer

2 Answers

you can try

SELECT * FROM #ReadCmd
WHERE Result LIKE '%:%' and Result like 'Name %';

if you want select only the info after the : then you should use

SUBSTRING(Result, CHARINDEX(':',Result) +2, 255)
from #ReadCmd
WHERE Result LIKE '%:%' and Result like 'Name %';

5

Select RIGHT(ColumnName,CHARINDEX(':',ColumnName) -1)

See SQL string manipulation [Get all text left of '('] for reference.

That is unless I am misunderstanding your question and you want to keep Name and the : in there and just remove the spaces. If that is so your second and fourth non code lines are contradictory.

7

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.