How can I Replace using the sql wildcard '%'?

Question

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

Solution 1

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 %';

Solution 2

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.

Comments

[TheEggSample]

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

[TheEggSample]

Sorry, accidentally tabbed and entered somehow ^^

[TheEggSample]

But here is my output I'm getting in the #output temp table: code( : SULESRKMA : PrimoPDF oft XPS Document Writer : Fax : CutePDF Writer) So why is Microsoft XPS being cut like that?

[Admin]

I couldn't tell by your data up top if those were different rows for each one or not. My example will not work without it being different rows. If it just one giant blurb of text it will be a bit more difficult.

[TheEggSample]

I'm new here, still trying to figure out the markdown. ` : SULESRKMA : PrimoPDF oft XPS Document Writer : Fax : CutePDF Writer`

[TheEggSample]

They are different rows. Sorry, I should have specified. I pulled the data from powershell and inserted it into the temp table #readcmd as per line. I'm taking the data from there

[TheEggSample]

Ah. I need to use 'shift + enter' to convey what I wanted to. The results are as follows in the #output temp table: : SULESRKMA : PrimoPDF oft XPS Document Writer : Fax : CutePDF Writer

[TheEggSample]

I'm trying to get just the values of these fields, so I'm going to select all the values, in this case 'SULESRKMA', 'PrimoPDF', 'Microsoft XPS Document Writer', 'Fax', and 'CutePDF Writer'. I want to ditch the parts like 'Name : '

[ScaisEdge]

@SaniT404 explain better your comment .. i don't undestand .. my answer i wrong ? .. or is incomplete ?

[TheEggSample]

So I have the cell 'Name : SULESRKMA' I want to select 'SULESRKMA' not 'Name : '

[TheEggSample]

Thanks! that got me on the right track. The actual code I had to use was: Select SUBSTRING(Result, CHARINDEX(':',Result) +2, 255)

[ScaisEdge]

@SaniT404 . correct for Tsql is charindex .. i have update the answer