-1

I have an SQL table that contains a list of categories. Each item starts with a number (the main category number), or a number "dot" another number (main category number and its subcategory number).

For example

10 Main Category A------
10.10 Sub Category A1
10.20 Sub Category A2
20 Main Category B
20.10 Sub Category B1
...

First I need only all entries that have main category numbers, in another query I have to filter only for subitems, that starts with e.g. 10 and give back all entries 10.10 and 10.20

The program I'm making needs to first select the Main category and based on the selected main category select the subcategories. There is no link in database between entries (parent-child) and only a sorting id e.g.

[0-9][0-9] I know, but then how to check for a possible (but not present third digit) and then NO following dot.

AlexdD1979
  • 13
  • 4

3 Answers3

0

This should give you all main category numbers:

select * 
from yourtable 
where 
    isnumeric(left(yourcolumn, charindex(' ', yourcolumn)-1))=1 and 
    left(yourcolumn, charindex(' ', yourcolumn)-1) not like '%.%'

Then join those to subs on first two digits

gunr2171
  • 16,104
  • 25
  • 61
  • 88
Daniel Marcus
  • 2,686
  • 1
  • 7
  • 13
0

Only the main categories: [0-9]+ +Category.*

Only the sub categories: [0-9]+/.[0-9]+ +Sub +Category.*

If you are working in SQL, using LIKE such as @DanielMarcus suggests would probably be the way to go.

Also, note [0-9][0-9] is redundant. Try [0-9]{2} (if expecting a number from 0-99) or [0-9]+ (if expecting any positive integer)

cacti5
  • 2,006
  • 2
  • 25
  • 33
  • This looks quite good, but only works in RegEx Tester for JS etc, but not for SQL regex. For main categories it does not work perfectly, the subcategories are also selected. For subcategories it is ok, but does not work for SQL regex. Is there a difference to use RegEx in MS SQL? – AlexdD1979 May 22 '18 at 06:42
  • What type of regex does SQL server use? It may be good to add that as a tag. I think the escape character should be changed to `/`. Additionally, you are getting matches on the subcategory with the category regex because it is doing a partial match such as `10 Sub Category A1` - updated, let me know if that works. – cacti5 May 22 '18 at 15:20
  • It is MS SQL 2016 `where description like '[0-9][0-9][\.]%' --and description like '10.%'` works great, until the numbers are lower than 99. The typing "category" was just for example, it could be anything of wording. `[0-9]{3}` does not work to cover values from 1 to 999 for main categories I didnt found a solution yet for MSSQL. Why SQL Regex works so different from standard regex? – AlexdD1979 May 22 '18 at 17:28
  • From [this question](https://stackoverflow.com/questions/29206404/mssql-regular-expression) "MS-SQL supports only a very limited subset of RegExp like operators". See https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms187489(v=sql.105) – cacti5 May 24 '18 at 17:49
0

To get all categories that are main categories, use:

select t.*
from t
where category not like '%.%';

To get the direct subcategories of a category use:

select t.*
from t
where category like '10.%' and category not like '%.%.%'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you, that works, but it is a little bit unsecure, if there is a text also inside with a dot. I would prefer more or less to use RegEx to filter for exact pattern. – AlexdD1979 May 22 '18 at 06:37