Trimming varchar(255)

[knerve1]

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,

 

[mrdenny]

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)

http://www.mrdenny.com

 

[djj55]

Is your Filler Order ID always the same number of characters?

 

[ptheriault]

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.

 

[knerve1]

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

 

[ESquared]

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]

 

[ESquared]

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]

 

[yaanmonkey]

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

 

[knerve1]

Esquared,

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

Yaanmonkey,
This totally worked. Thank you very much!

 

[ESquared]

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]