question

Chris 2 avatar image
Chris 2 asked

When LTRIM and RTRIM don't work

Hi,

On Windows 2003 and SQL 2005 SP2, I am trying to import a pipe delimited text file into database. One of file has one extra non space character after the pipe before the date:

| 20100415|

which caused this string function into trouble:

LEN(LTRIM(RTRIM(AccessedDate))) > 1 && LEN(LTRIM(RTRIM(AccessedDate))) == 8 ? SUBSTRING((DT_STR,20,1252)(AccessedDate),1,4) + "/" + SUBSTRING((DT_STR,20,1252)(AccessedDate),5,2) + "/" + SUBSTRING((DT_STR,20,1252)(AccessedDate),7,2) : ""

RTRIM could not trim leading non-space character. Is there a way to get rid of that character? Thanks.

Chris

sql-server-2005ssis
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

You should be able to just use the substring function. Substring(accesseddate, 2, len(accesseddate)) for instance will just drop the very first character and return the rest of the string so you cna process it.

If you this extra character is not always present, then you can use an if statement to determine whether or not you need to drop that first character and then branch to the two cases.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Chris 2 avatar image Chris 2 commented ·
Thanks for helping out. How do you do that IF in dericed column editor? Or how to add checking for first character is numeric in this experssion: LEN(LTRIM(RTRIM(AccessedDate))) > 1 && LEN(LTRIM(RTRIM(AccessedDate))) == 8 ? SUBSTRING((DT_STR,20,1252)(AccessedDate),1,4) + "/" + SUBSTRING((DT_STR,20,1252)(AccessedDate),5,2) + "/" + SUBSTRING((DT_STR,20,1252)(AccessedDate),7,2) : "" Many thanks
0 Likes 0 ·
dvroman avatar image
dvroman answered

If you know the character you can do a REPLACE function. If the oddball character changes, it's best to use the SUBSTRING function. Most frequently this character is a non-breakable space xA0, normally this happens when the data is copied from a web page which contains a   code.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered

Just as a general point, the CONVERT function will do all this for you, even if there is leading whitespace. 112 is the code for the ISO dqte with a four-figure year. (see http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx for details!)

  select convert(DateTime,' 20100415',112)

and here is a safe way of trimming any gloop from the start of a string and converting it to a date. Here we stick in a line break just to illustrate the point

  Select convert(DateTime,substring(string, patindex('%[0-9]%', string),8000),112) from (Select '
   20100415' as string)f
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.