25
Views
5
Comments
Solved
Convert string with "ABC,EFG,XYZ" to 'ABC','EFG','XYZ'
Application Type
Traditional Web

Hi 

I am having a scenario where I am passing a text type(@Name) input parameter in sql. This input parameter is getting used in where clause with in condition like select * from tblName where FirstName in @Name.

I am using an input control to pass the value for @Name parameter. The problem is that the value which I received is not in comma separated form for eg "ABC,EFG,XYZ", I want this string in comma separated format like 'ABC','EFG','XYZ' so that I can use it in my sql with in condition. Please suggest how to achieve it.


Thanks in advance.

Solution

Hi Vivek,

You can use below option also before passing it to the sql query:

"'"+Replace(CurrentValue,",","','")+"'"


"CurrentValue" is the variable.


Hope this helps you !!


Regards,

LK

Rank: #70

Hi Vivek,


You can use Regex_Replace from Text extension to achieve that:


You can create a function that receives an input and applies your changes and return an output:


You call the Regex_Replace function with the text you want to change as the Input,  "," as the pattern you want to replace, and "','" as the Replacement so you can include your quotes.


Then you just have to add the quote to the beginning and end of the text:


Hope it helps.


Kind Regards,
João

Solution

Hi Vivek,

You can use below option also before passing it to the sql query:

"'"+Replace(CurrentValue,",","','")+"'"


"CurrentValue" is the variable.


Hope this helps you !!


Regards,

LK

Rank: #388

Hi Vivek,

If you have a SQL Server 2016 or later and a Compatibility_Level at least 130, you can use STRING_SPLIT function to do this.

Example:

 select * from tblName where FirstName in (select value from STRING_SPLIT(@Name,','))


Regards, 

Fabrice

Rank: #469

Hi Vivek 

If you want to use in sql in clause, then you can use String_Split and BuildSafe_InClauseTextList.

You can refer to URL below, It's also recommended by Outsystems.

https://success.outsystems.com/Documentation/Best_Practices/Development/Building_Dynamic_SQL_Statements_the_Right_Way

Best Regard

Rank: #12879

Hi All . Thanks for your precious replies. I have tried the above solution and worked for me. 

Regards