Trim in custom SQL

building reports webintelligence
1

I want to trim all leading and trailing spaces for every dimension in a query, using custom SQL on MS-SQL DB, what i have tried thus far is generating errors on validate just for one dimension? any suggestions

Tony_C_Florida (BOB member since 2014-09-28)

2

Don’t know MS-SQL DB, so maybe a stupid question but; are you sure your Dimension is not a string?

Stef102 :belgium: (BOB member since 2014-08-05)

3

Hi,

Can you please post what you have tried so far and it did not work?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

I’d recommend doing it at the ETL stage rather than within the universe.

Are all objcets that need trimming varchar/char fields or are numbers and dates in there as strings with leading/trailing spaces?

5

fields seem to be both Text / char and number. Some appear empty but have char in them. Need to trim.

Have tried LTRIM(RTRIM(

Give me a error 'The data type of a column in the query is not valid" IES 10811)

Seems MS SQL does not have a general trim. Yes it would be better to do in ETL or Universe but not an option for this at this time. Would be a major project I need answers this week.

Tony_C_Florida (BOB member since 2014-09-28)

6

Start creating views

Use a combination of ltrim(rtrim(colname)) and casting/converting to get the table you want:

create view v_table1 AS
select
cast(ltrim(rtrim(col1)) as int) col1_int,
cast(ltrim(rtrim(col2)) as varchar(20)) col2_vc20,
…
cast(ltrim(rtrim(col10)) as date) col10_date
from
table1 WITH (NOLOCK)