Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trimming varchar(255)

Status
Not open for further replies.

knerve1

Programmer
Mar 28, 2005
20
US
Anyone,

What would be the easiest statement to trim this?

Filler Order ID: <10190968MMDSCR> Filler Facility ID: <RAD>


I only want what is in between the < > to then compare to another column.

Thank you,
 
You will want to use SUBSTRING and CHARINDEX.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Is your Filler Order ID always the same number of characters?
 
What do you want your result set to look like?

This
10190968MMDSCRRAD

Or This

10190968MMDSCR RAD


- Paul
- If at first you don't succeed, find out if the loser gets anything.
 
The filler order id is always 13 characters. The problem is the < lands in different positions for each record. IE:



Placer Order ID:<1020517276830> Placer Facility: <RAD>


Filler Order ID: <1018863276856> Filler Facility ID: <RAD>


I would like it to look like this:

1020517276830
1018863276856

I have tried the charindex and am trying to incorporate a substring but with no success.

My query:

SELECT
charindex('<',dbo.CV3OrderStatusHistory.ReasonText)rt , dbo.CV3OrderStatusHistory.ReasonText,
dbo.CV3Client.DisplayName, dbo.CV3OrderStatusHistory.CreatedWhen,
dbo.CV3OrderStatusHistory.OrderStatusCode, dbo.CV3ClientVisit.VisitIDCode

FROM
dbo.CV3Client INNER JOIN
dbo.CV3OrderStatusHistory ON dbo.CV3Client.GUID = dbo.CV3OrderStatusHistory.ClientGUID
INNER JOIN
dbo.CV3ClientVisit ON dbo.CV3Client.GUID= dbo.CV3ClientVisit.ClientGUID
WHERE
(dbo.CV3Client.DisplayName = 'XTEST, TESTONE' OR
dbo.CV3Client.DisplayName = 'XTEST, TESTTWO')

AND (dbo.CV3OrderStatusHistory.CreatedWhen >= '20070101')
AND
(dbo.CV3OrderStatusHistory.CreatedWhen < '20070216') AND (dbo.CV3OrderStatusHistory.ReasonText LIKE 'Filler Order ID:%' OR dbo.CV3OrderStatusHistory.ReasonText LIKE 'Placer Order ID:%')
AND (dbo.CV3OrderStatusHistory.OrderStatusCode = 'PERF' OR dbo.CV3OrderStatusHistory.OrderStatusCode = 'SCHD') AND (dbo.CV3ClientVisit.VisitIDCode = '111111111' OR
dbo.CV3ClientVisit.VisitIDCode = '222222222')

ORDER BY dbo.CV3ClientVisit.VisitIDCode

This gives me:
17 Placer Order ID:<1020517276830> Placer Facility: <RAD> Filler Order ID: <1020517276830> Filler Facility ID: <RAD> XTEST, TESTONE 2007-01-26 13:05:36.127 SCHD 111111111
18 Filler Order ID: <1018863276856> Filler Facility ID: <RAD> XTEST, TESTONE 2007-01-26 13:10:11.170 PERF 111111111

Alternating bold is to break up records

Thank you
 
If you join CV3OrderStatusHistory to table CV3AncillaryOrderID on OrderGUID = OrderGUID you can just use the OrderID column. Why parse it out of the text?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
By the way, I am not 100% sure that's right, I found it by searching. But it seems to match based on my queries. Ask Eclipsys.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
This should return just the numeric:

SELECT SUBSTRING('Filler Order ID: <10190968MMDSCR> Filler Facility ID: <RAD>',CHARINDEX('<','Filler Order ID: <10190968MMDSCR> Filler Facility ID: <RAD>')+1,CHARINDEX('>','Filler Order ID: <10190968MMDSCR> Filler Facility ID: <RAD>')-CHARINDEX('<','Filler Order ID: <10190968MMDSCR> Filler Facility ID: <RAD>')-1)

You obviously need to replace the:

'Filler Order ID: <10190968MMDSCR> Filler Facility ID: <RAD>'

with the actual field name throughout the statement and drop the SELECT from the start that was just for testing . it should look more like:

SELECT Field1, Field2, SUBSTRING(Field3,CHARINDEX('<',Field3)+1,CHARINDEX('>',Field3)-CHARINDEX('<',Field3)-1) AS Field3ID
FROM TABLE1

Hope this helps



 
Esquared,

The AncillaryOrderID Table is EXACTLY what I needed! Thank you.

Yaanmonkey,
This totally worked. Thank you very much!




 
knerve1,

You could as easily have found that table by doing a search for columns in the database like '%order%id%'.

And also, isn't there anyone else at your company that you can ask for help with SXA?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top