Checking a string if it's a number or alphanumeric

[Elhadji_M_Ba]

I have a field sometimes it has numbers only and sometimes it has a string of alphanumeric values like 12b or 3a or 23b etc. I want to test the field to see if it has a number then I will just return that number. But if after testing it, it has an alphanumeric value I would like to split the letters and the numbers. I hope the explanation makes sense. Don’t hesitate to ask for clarification. Many thanks

[kevinadi (Kevin Adistambha)]

Hi @Elhadji_M_Ba welcome to the community.

Could you provide an example of what you need? Also, are you looking to do this using the mongo shell, or using some language (node, python, etc.)? An input → output example would be helpful here.

As an immediate answer, you should be able to do this using regular expressions. See $regex for MongoDB’s operator, or Regular Expression if you’re not familiar with the subject.

Best regards,
Kevin

[Elhadji_M_Ba]

ok here is an example. I hope it helps. thanks

//Current - numeric portion can be 1 or more digits

[{
  "agendanum": "70"
},{
  "agendanum": "70a",
  "agendasubject": "RIGHTS OF THE CHILD"
},{
  "agendanum": "70b",
  "agendasubject": "CHILDREN--UN. GENERAL ASSEMBLY (27TH SPECIAL SESS. : 2002)"
},{
  "agendanum": "71a",
  "agendasubject": "INDIGENOUS PEOPLES"
},{
  "agendanum": "71b",
  "agendasubject": "INDIGENOUS PEOPLES--CONFERENCE (2014 : NEW YORK)"
}]

//Ideal

[{
  "agendanum": [
	"70"
	]
},{
  "agendanum": [
	"70",
	"a"
	]
  "agendasubject": "RIGHTS OF THE CHILD"
},{
  "agendanum": [
	"70",
	"b"
	]
  "agendasubject": "CHILDREN--UN. GENERAL ASSEMBLY (27TH SPECIAL SESS. : 2002)"
},{
  "agendanum": [
	"71",
	"a"
	]
  "agendasubject": "INDIGENOUS PEOPLES"
},{
  "agendanum": [
	"71",
	"b"
	]
  "agendasubject": "INDIGENOUS PEOPLES--CONFERENCE (2014 : NEW YORK)"
}]

[kevinadi (Kevin Adistambha)]

Hi @Elhadji_M_Ba,

Apologies for the late reply. Looking at your example, if you’re looking to permanently transform the shape of all the documents in the collection, I would personally do this via a general purpose language e.g. Python, since I think it will be easier to test and extend.

However if you only want to project the result, you may be able to achieve it using $regexFind, which allows match captures. Something like:

> db.test.find()
{ "_id" : 0, "num" : "70a", "txt" : "Desc 1" }
{ "_id" : 1, "num" : "70b", "txt" : "Desc 2" }

> db.test.aggregate([
...   {$project: {
...     ret: {
...       $regexFind: {input: '$num', regex: /([0-9]+)([a-z]+)/}
...     },
...     txt: '$txt'
...   }},
...   {$project: {
...     num: '$ret.captures',
...     txt: '$txt'
...   }}
... ])
{ "_id" : 0, "num" : [ "70", "a" ], "txt" : "Desc 1" }
{ "_id" : 1, "num" : [ "70", "b" ], "txt" : "Desc 2" }

Please note that you need MongoDB 4.2 or newer for this. The regex pattern above also assumes the pattern to follow exactly the example, so it may need further refinements for more complex examples.

Best regards,
Kevin