How to get only number of issue

When i use KEY variable a get string with full key issue 'BFD-1234'

I need to have only issue numer to put a parameter in SQL query. It's possible to do in dedicated metohod or I need to use replace function ?

4 answers

1 accepted

Hi Konrad,

You can use the matchText routine for it. It's returning a string, but SIL is smart enough to convert it to a number, if you want.

number x = matchText(key, "\\d+");

Hi Konrad,

There is no dedicated SIL routine for retrieving only the number part of an issue key.

However, you can use the string replace routine, as you mentioned:

replace(key, project + "-", "")

or the substring routine:

substring(key, indexOf(key, "-") + 1, length(key))

0 vote

Issue numbers (the bit after the - in the key) are NOT unique, so it's pretty much pointless using them. You should be using the full Key, or you could get duplicates.

If you really are looking to get multiple issues with the same numbers in their key, then yes, you'll need to manipulate the data to split off the project key before using the number.

Yes it's not unique if you don't use join select query with project table.

I need to get ID from jiraissue table where issenumber is part of key (number part) so I want to have only issue number

But that's the point - if you select on just the issue-key numerical part, you can get several results. Issues ABC-123, XYZ-123 and JRA-123 if you selected for "123"

You don't need to join to the project table either - the key already contains the project short-code.

I'm actually quite curious as to why you want this - I can't think of any real use for it. Unique issues, yes. Exposing the database ID to use that instead, yes (and I've written code to help with that). But selecting potentially multiple issues on the numerical part of the issue key seems pretty pointless. I suspect it's just my imagination letting me down. Indulge an old hacker, I'd be really grateful if you could explain why you're doing this?

Since JIRA 6.x jiraissue table don't store pkey column - it's always null. So You can't query like this:

SELECT ID  FROM jiraissue  WHERE pkey = '<all string KEY>'

You need query like this:

SELECT ID  FROM jiraissue  WHERE issuenum = <number part of KEY> AND project = (SELECT PROJECT_ID FROM project_key WHERE PROJECT_KEY = '<string part of KEY>')

to get ID of only one issue.

"I'm actually quite curious as to why you want this " - I want this to get array with username who votes in issue. To do that I need ID from issue (SQL table ID).

So whole my code looks like this:

SELECT [SOURCE_NAME] 
FROM [userassociation]
WHERE ASSOCIATION_TYPE = 'voteIssue' AND SINK_NODE_ID = 
(SELECT ID  FROM jiraissue  WHERE issuenum = <number part of KEY> AND project = 
(SELECT PROJECT_ID FROM project_key WHERE PROJECT_KEY = '<string part of KEY>'))

Right, so you are using the project key part of the string. That makes sense now.

This sql query will be unique:

SELECT ID  FROM jiraissue  WHERE issuenum = <number part of KEY> AND project = (SELECT PROJECT_ID FROM project_key WHERE PROJECT_KEY = '<string part of KEY>')

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published May 30, 2018 in Marketplace Apps

Three tips for boosting your board's efficiency with Story Maps

Trello is one of the most effective tools for driving your sprints. It's customizable for every Agile team and product owners and Scrum masters (SM) love it. However, Agile teams often struggle with:...

834 views 2 9
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you