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

This widget could not be displayed.

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+");
This widget could not be displayed.

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))

This widget could not be displayed.

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 widget could not be displayed.

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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Published Jul 25, 2018 in Marketplace Apps

Jira Cloud and Bitbucket Cloud Integration with Microsoft Teams

One of the newest products in the Microsoft family - Microsoft Teams,  is a chat-based hub for teamwork that integrates all the people, content, and tools your team needs to be more engaged and ...

720 views 0 3
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