How to get only number of issue

[Kondi]

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 ?

[Silviu Burcea · Accepted solution]

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+");

[Alexandru_Iacob]

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

[Kondi]

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

[Nic Brough -Adaptavist-]

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.

[Kondi]

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

[Nic Brough -Adaptavist-]

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?

[Kondi]

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

[Nic Brough -Adaptavist-]

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