Problem with MSSQL Integration - "/" is not accepted

[GRAT]

Hey guys,

I am having trouble with the MSSQL integration: some numbers in our databse contain "/" as a divider between area code and number, with some numbers its "-".

"/" ist not accepted by 3cx, the log says invalid content in the phone mobile field and therefore dismisses the field. As a result opf the dismissed phone number field, the whole process stops.

My questions are:

- Is there a 3cx related setting that could help?
or
- Can the SQL Statement be modified so that "/" is replaced by something else or removed?

Thanks in advance for your replies!

Best regards,

G

 

[GRAT]

Hey,

so far I was able to manage to find out that the SQL command TRIM could be the solution to my problem - but I just don´t get the syntax right.

Any help is appreciated!

Thanks,

G

 

[Evolute IT]

DM me your query, I'll adjust it for you. There are SQL functions for that as well as 3CX ones.

 

[GRAT]

Found a solution. Because "-" is ok but "/" is not, I replaced all the "/" with "-":

SELECT adr_snr as contactid, REPLACE(tel2, '/', '-') as phonebusiness2, werbe_name1 as companyname, RTRIM(v_name) as firstname, RTRIM(n_name) as lastname, mail as email, REPLACE(tel, '/', '-') as phonebusiness, REPLACE(handy, '/', '-') as phonemobile, REPLACE(fax, '/', '-') as faxbusiness FROM adr_pers_v WHERE tel LIKE '%[Number]%' or handy like '%[Number]%' or  tel2 LIKE '%[Number]%'

With n_name and v_name I used RTRIM to remove blank spaces.

 

[GRAT]

Hi Mr. Marcoux,

thanks for your reply!

I know recognized that I do not want "/" or "-" in the results, because shorter numbers will not be recognized by the system. My guess would have been

REPLACE(REPLACE(tel2, '/', '') '-', '') as phonebusiness2

but it just gives me the error

Wrong syntax close to "-"

What am I missing or is there a better way to get rid of "-" and "/"?

 

[Evolute IT]

You're missing a comma after the 2nd REPLACE

 

[GRAT]

Thanks!

SELECT CONCAT(adr_snr, pers_cd) as contactid, REPLACE(REPLACE(REPLACE(tel2, '/', ''), '-', ''), ' ', '')  as phonebusiness2, werbe_name1 as companyname, COALESCE(NULLIF(RTRIM(v_name),''), werbe_name1) as firstname, RTRIM(n_name) as lastname, mail as email, REPLACE(REPLACE(REPLACE(tel, '/', ''), '-', ''), ' ', '') as phonebusiness, REPLACE(REPLACE(REPLACE(handy, '/', ''), '-', ''), ' ', '') as phonemobile, REPLACE(REPLACE(REPLACE(fax, '/', ''), '-', ''), ' ', '') as faxbusiness FROM adr_pers_v WHERE tel LIKE '%[Number]%' or handy like '%[Number]%' or  tel2 LIKE '%[Number]%'

After understanding the syntax I was also able to additionally remove blank spaces from the numbers.