String function fetching a number

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 2005 Forums

Transact-SQL (2005)

String function fetching a number

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-08-31 : 15:05:02

If after second dot from left if it is UID then get the second set number which si 680, otherwise ignore.

Is it possible? i have almost 80k rows with filenames. need to fetch the second set number which is between first dot and second dot from left with condition after second dot if there is UID.....

235.680.UID13-1269530731_Allworx Installation

Thank you very much for the helpful info.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-31 : 15:33:45

here's one way

Declare @tbl table (val varchar(128))
Insert @tbl VALUES ('235.680.UID13-1269530731_Allworx Installation')

SELECT	Substring(val, charindex('.', val) + 1, charindex('.', val, charindex('.', val) + 1) - charindex('.', val)-1)
FROM	@tbl
WHERE	Substring(val, charindex('.', val, charindex('.', val) + 1)+1, 3) = 'UID'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-01 : 05:31:24

If the pattern is same

select PARSENAME(val,2) from @tbl
where PARSENAME (val,1) like 'UID%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources