CODEWITHSUNDEEP
sqlsql-servertrim
user1342164
user1342164

Reputation: 1454

SQL query trim a column?

I am using the following query to populate some data. From column "query expression" is there a way to remove any text that is to the left of N'Domain\

Basically I only want to see the text after N'Domain\ in the column "Query Expression" Not sure how to do this.

SELECT
  v_DeploymentSummary.SoftwareName,
  v_DeploymentSummary.CollectionName,
  v_CollectionRuleQuery.QueryExpression
FROM
v_DeploymentSummary
INNER JOIN v_CollectionRuleQuery
ON v_DeploymentSummary.CollectionID = v_CollectionRuleQuery.CollectionID

Upvotes: 0

Views: 306

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In SQL Server, you can use stuff() for this purpose:

SELECT ds.SoftwareName, ds.CollectionName,
       STUFF(crq.QueryExpression, 1,
             CHARINDEX('Domain\', rq.QueryExpression) + LEN('Domain\') - 1,
             '')
FROM v_DeploymentSummary ds INNER JOIN
     v_CollectionRuleQuery crq
     ON ds.CollectionID = crq.CollectionID;

Note the use of table aliases makes the query easier to write and to read.

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44326

Try this, It will not change values for QueryExpression without the text N'Domain and does not require that N'Domain is the first text:

SELECT
  ds.SoftwareName,
  ds.CollectionName,
  crq.QueryExpression
  STUFF(crq.QueryExpression, 1, charindex('N''Domain\', 
    REPLICATE('w', LEN('N''Domain\')-1) + crq.QueryExpression), '')
FROM
  v_DeploymentSummary ds
JOIN 
  v_CollectionRuleQuery crq
ON ds.CollectionID = crq.CollectionID

Example:

SELECT 
  STUFF(x, 1, charindex('N''Domain\', 
    replicate('w', LEN('N''Domain\')-1) + x), '')
FROM (values('N''Domain\xxx'),('N''Doma'),('xxN''Domain\yyy')) x(x)

Result:

xxx
N'Doma
yyy

Upvotes: 1

alan07sl
alan07sl

Reputation: 61

At least for SQL Server:

SUBSTRING([v_CollectionRuleQuery.QueryExpression], CHARINDEX('N''Domain\', [v_CollectionRuleQuery.QueryExpression]) + 9, LEN([v_CollectionRuleQuery.QueryExpression])

Give it a try.

I didn't understand if you wanted to include N'Domain\ in your string if that's the case just remove the +9.

In my understanding you want something like this:

SELECT
  v_DeploymentSummary.SoftwareName,
  v_DeploymentSummary.CollectionName,
  SUBSTRING([v_CollectionRuleQuery.QueryExpression], CHARINDEX('N''Domain\', [v_CollectionRuleQuery.QueryExpression]) + 9, LEN([v_CollectionRuleQuery.QueryExpression])
FROM
v_DeploymentSummary
INNER JOIN v_CollectionRuleQuery
ON v_DeploymentSummary.CollectionID = v_CollectionRuleQuery.CollectionID

Upvotes: 1

Related Questions