getting part of 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)

getting part of string

Author

Topic

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-03-24 : 14:11:08

Hello,

I hope someone can help me with this. I need to get a part of a string out of a given string.. and No it is not as easy as using SUBSTRING(). Here goes.
Table:
Names
Math v1.0
English v1.0.0 class
Geology 7.2 for beginners
New 3.2.4 intermediate

Out of each value I want to read out just the numbers (versions).
From above I need to have the following:
1.0
1.0.0
7.2
3.2.4

Thanks in advance

Juls.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 14:14:43

Juls --

not that this solves your immediate problem, but hopefully you can see that in the future you should store the version # in a separate field (or fields), and just concatenate the together as needed.

It is a lot easier to put things TOGETHER than it is to break them apart ...

You will need to find the first space and the 2nd space in each field, and take what is in the middle and that is your version #.

Easiest way: create a user defined function that loops through the string and returns what you need.

- Jeff

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-03-24 : 14:24:27

Thanks Jeff,

That is what I am trying to do... keep versions separately. The Data was given to me to be pulled apart so that now and in the future to keep the two separately.

Unfortunately, the version number in every string is not always located after the first space.

I can have values like:

my new class v1.2
classic store 2.0.0
geo in 5.6 version

There is no logic to these strings.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-24 : 18:58:54


SELECT Names, CASE WHEN spos > 0 THEN SUBSTRING(Names, spos,
  PATINDEX('%[^.0-9]%', SUBSTRING(Names, spos, 8000)+'x')-1) END
FROM (
    SELECT Names,
      PATINDEX('%[0-9]%', Names) AS spos
    FROM (
        SELECT 'Math v1.0' AS Names
        UNION ALL SELECT 'English v1.0.0 class'
        UNION ALL SELECT 'Geology 7.2 for beginners'
        UNION ALL SELECT 'New 3.2.4 intermediate'
        UNION ALL SELECT 'No numbers in this one'
      ) AS A
  ) AS A

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-03-25 : 16:42:44

Works like a charm.

Thanks SOOO MUCH.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources