topic Re: Transform CHAR(255) to VARCHAR(255) SQL Server in Qlik Replicate

Transform CHAR(255) to VARCHAR(255) SQL Server

RandyWeis — Tue, 16 Jan 2024 21:43:37 GMT

We are using a third party product using SQL Server, which has many CHAR(255) columns that often are all spaces or are a lot smaller than 255. QLIK Replicate retains the CHAR data type on the Target. When using QLIK Replicate, I want to change/transform the Data Type from CHAR(255) to VARCHAR(255), and TRIM the data to make the DB more efficient. I looked at the Replicate Transform Rules, but looks like I can only change from/to "STRING" not the specific Target SQL Server (or Snowflake) DBMS Data Types, such as CHAR or VARCHAR.

Is there a way to do this using Replicate?

Re: Transform CHAR(255) to VARCHAR(255) SQL Server

Dana_Baldwin — Tue, 16 Jan 2024 22:56:04 GMT

Hi @RandyWeis

There is an internal / intermediate data type used by Replicate which is then mapped to a compatible data type on the target. Check the source data type mapping (SQL Server source Char is internally handles as String)...

Microsoft SQL Server source data types #Microsoft SQL Server source data types | Qlik Replicate Help

...and compare to the target (SQL Server target should use Varchar(length))

Microsoft SQL Server target data types #Microsoft SQL Server target data types | Qlik Replicate Help

...and Snowflake should also use Varchar (there's some details around the length it will use as well as other outcomes if source data is JSON for example)

Snowflake on AWS data types | Qlik Replicate Help

When doing a transformation in a task on the data type you can only choose the internal Replicate data type, not the data type it will eventually become on the target.

Next, you should be able to use a trim function in a transformation to get rid of the extra spaces. I'll reply again with a syntax example.

Thanks,

Dana

Forgot to mention - you can change the version number of the documentation at the top left of the screen, but I don't think there's been any recent changes to data types.

Re: Transform CHAR(255) to VARCHAR(255) SQL Server

Dana_Baldwin — Tue, 16 Jan 2024 23:16:36 GMT

@RandyWeis sounds like you may want to set this globally rather than one column at a time, but for just one column:

1. Open the table from the designer tab by double clicking it on the bottom right area, "selected tables"

2. Select the transform tab on the left

3. For the column to modify click the fx icon in the expression box. The Expression Builder will open.

4. Select the Functions tab

5. Select string at the top (should already be selected) then click trim in the next column and use the arrow to select it.

6. Place the column name with a dollar sign in front of it in the parentheses:

7. click Parse to confirm the syntax structure

8. Enter a value to test with leading spaces (makes the test result easier to confirm) & click Test Expression

9. Save your changes

Pretty sure this can be set globally, checking on exact steps.

Re: Transform CHAR(255) to VARCHAR(255) SQL Server

Dana_Baldwin — Tue, 16 Jan 2024 23:25:24 GMT

@RandyWeis

For the global transformation:

To set this globally (potentially affecting all string columns in the task):

1. Open Global Rules on the designer tab of the task

2. Click New Rule and select Transformation

3. Scroll down & select Replace column value

4. Set the scope by indicating schema, table or column name (to optionally limit what is changed). Select your data type, string in this case. Click next.

5. Click fx for the Replace target value with.

6. Select Functions, Strings, and Trim as before, only for the column name use the variable $AR_M_SOURCE_COLUMN_NAME:

Test as mentioned earlier

7. Click OK to close Expression Builder, click Finish to close the wizard, and click OK to close the Global Rules screen.

Re: Transform CHAR(255) to VARCHAR(255) SQL Server

DesmondWOO — Wed, 17 Jan 2024 04:59:38 GMT

Hi @RandyWeis ,

If you are looking to alter the structure of TARGET table from CHAR to VARCHAR in SQL Server, this post might be helpful.

Regards,
Desmond