Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

New Member

DBRead select statement with substring

Hi guys,

can anybody give me SQL support because I try to use the following select query on the DB Read step in UCCX script.

...

SELECT * FROM [DBName].[dbo].[Table1]

WHERE SUBSTRING(TELEPHONNUMBER,1,LEN(TELEPHONNUMBER) - 1) = $sCallingNumber

...

I try to cut the last digit from telephonenumber field but not only once.

If I use this select query it works for the first cut but if I want to cut the second digit from the right side of the telephonenumber

I would have to use this select query:

...

SELECT * FROM [DBName].[dbo].[Table1] 

WHERE SUBSTRING(TELEPHONNUMBER,1,LEN(TELEPHONNUMBER) - 2) = $sCallingNumber

...

But I would like to use the same DB Read step in the script with a counter variable of the UCCX routing script.

Therefore I would like to use a select query like:

...

SELECT * FROM [DBName].[dbo].[Table1] 

WHERE SUBSTRING(TELEPHONNUMBER,1,LEN(TELEPHONNUMBER) - $counter) = $sCallingNumber

...

But this doesn't work. I get an error message that the "SQL Statement Variable is not defined" but I have defined

this variable in the UCCX script. Does anybody know why I get this error message?

 

Thanks,

Tobias

1 ACCEPTED SOLUTION

Accepted Solutions

Thanks for you patience..I'm

Thanks for you patience..I'm pretty sure I found a solution..and it's all about the ) after the variable..put the data in the Step like the below..notice the white space btw $counter and paren ):

SELECT * FROM [DBName].[dbo].[Table1] 
WHERE SUBSTRING(TELEPHONNUMBER,1,LEN(TELEPHONNUMBER) - $counter ) = $sCallingNumber
4 REPLIES

Unfortunately, I've never

Unfortunately, I've never found a good guide on what these DB Steps can and can't do with the exception of select count(*) and select min(*) are not useable..

Out of curiosity..how are you determining the value you are setting on the "counter" ? Based on the Length of the CallingNumber?..so..counter = CallingNumber.length() ?

New Member

I would like to cut the first

I would like to cut the first digit (from DB entry and from ANI) then compare these both and if there is no match set the counter = 2 in the routing script and cut 2 digits (from DB entry and from ANI) compare them and so on till the counter is 4 or so.

In routing script I use a separate var for cutting calling number

...

sCallingNumber.substring(0,(sCallingNumber.length()-1))

...

but so the var sCallingNumber is always the ANI without the last digit...

Any ideas? 

Thanks for you patience..I'm

Thanks for you patience..I'm pretty sure I found a solution..and it's all about the ) after the variable..put the data in the Step like the below..notice the white space btw $counter and paren ):

SELECT * FROM [DBName].[dbo].[Table1] 
WHERE SUBSTRING(TELEPHONNUMBER,1,LEN(TELEPHONNUMBER) - $counter ) = $sCallingNumber
New Member

Thanks Sam that's the

Thanks Sam that's the solution!

269
Views
5
Helpful
4
Replies