Find the index in a 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 2005 Forums

Transact-SQL (2005)

Find the index in a string

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-06-19 : 13:32:10

Hi,

I have a string that likes

RBA0092 001 0003 06/18/09 11:36 ABCD

I want to get the substring ABCD but I don't want to use split function. Because it is too complex.
So I am thinking to locate the index of

maybe a good choice. After that we can get

ABCD

Thanks for your input.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 13:35:56

Are you just trying to get the last four characters?

select right(@str,4)

Jim

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-19 : 13:36:18

If the ABCD value is always 4 characters then this will work:

SELECT reverse(substring(reverse('RBA0092 001 0003 06/18/09 11:36 ABCD'),0,5))

Some days you're the dog, and some days you're the fire hydrant.

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-06-19 : 13:39:01

No. I don't know how long of the last substring. But I only want to get it.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 13:42:32

If all you want is substring ABCD then select 'ABCD' will always work for you. Can you be more specific about what the problem is and what you hope to accomplish?

Jim

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-06-19 : 13:50:32

The last substrin may be ASDFD,46546,TYU687.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-19 : 13:52:57

This should work for you:


DECLARE @t TABLE(string VARCHAR(100))
INSERT INTO @t
SELECT 'RBA0092 001 0003 06/18/09 11:36 ABCD' UNION ALL
SELECT 'RBA0092 001 0003 06/18/09 11:36 ABCDEFG' UNION ALL
SELECT 'RBA0092 001 0003 06/18/09 11:36 ABC'

SELECT reverse(substring(reverse(string),1,charindex(' ', reverse(string)))) 
FROM @t

Some days you're the dog, and some days you're the fire hydrant.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-06-19 : 14:08:30

You can also do it this way as well:


SELECT substring(@string,(charindex(':',@string)+3),len(@string))

Some days you're the dog, and some days you're the fire hydrant.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-06-21 : 20:50:02

3 REVERSEs are a wee bit expensive in the face of scalability performance wise. If a space is always guaranteed in the string, then something as simple as the following will work with good performance.

DECLARE @t TABLE(string VARCHAR(100))
INSERT INTO @t
SELECT 'RBA0092 001 0003 06/18/09 11:36 ABCD' UNION ALL
SELECT 'RBA0092 001 0003 06/18/09 11:36 ABCDEFG' UNION ALL
SELECT 'RBA0092 001 0003 06/18/09 11:36 ABC'

SELECT RIGHT(String,CHARINDEX(' ',REVERSE(String))-1)
  FROM @t

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"
For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources