Stripping String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stripping String

Author

Topic

bi89405
Starting Member

35 Posts

Posted - 2009-11-15 : 20:36:21

I have a field name in my database thats stored in the following format:

Invoice 9999 for client ABC on 021909.pdf
Invoice 10000 for client DEF on 032309.pdf
Invoice 10001 for client GHI on 041209.pdf

I need to strip the above strings and return this:

9999
10000
10001

How can I do that?

Thanks in advance,
Zack H.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-16 : 02:03:06

If the data hase same format

select data,substring(col,9,patindex('%[ for]%',substring(col,9,len(col)))) as invoice_no from your_table

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 13:18:24


CREATE TABLE #myTable99 (Col1 varchar(256))
GO

INSERT INTO #myTable99
SELECT 'Invoice 9999 for client ABC on 021909.pdf' UNION ALL
SELECT 'Invoice 10000 for client DEF on 032309.pdf' UNION ALL
SELECT 'Invoice 10001 for client GHI on 041209.pdf'
GO


SELECT SUBSTRING(Col1
			,  CHARINDEX(' ', Col1)+1
			, (CHARINDEX(' ', Col1, CHARINDEX(' ', Col1)+1)) - (CHARINDEX(' ', Col1)+1)
)
FROM #myTable99
GO

DROP TABLE #myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bi89405
Starting Member

35 Posts

Posted - 2009-11-17 : 16:51:04

Brilliant work to both posts.

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources