OK, maybe this works:

declare @a varchar(100)

set @a = 'a.b.c.d.f.g'

SELECT REPLACE(SUBSTRING(@a, charindex('.', @a), LEN(@a) - charindex('.', REVERSE(@a))-1), '.','')

SELECT SUBSTRING(REPLACE('ab.cd.ef.gh.kl','.',''), 3, len(REPLACE('ab.cd.ef.gh.kl','.',''))-4)

SELECT SUBSTRING(REPLACE('hh.jj.kk.rr.ee.bb','.',''), 3, len(REPLACE('hh.jj.kk.rr.ee.bb','.',''))-4)

You didn't remove the left and right 2 characters 🙂

--edit aaah, just seen the latest post - that's tougher

My best guess is that the data is probably coming in as null from Excel (you can check this using a data viewer). Excel has 'decided' that that field contains...

So the requirement is to remove all the dots and remove the left 2 chars and the right 2 chars?

Would you like it in SQL or in script?

What is the datatype of the column to which you would like to pass this data?

Try removing the T2 aliasing and writing out the full name of the table. You cannot alias a table in the first line of an update statement, even in T-SQL...

Is the PK an IDENTITY field? If so, you'll have to enable IDENTITY insert.

bitbucket-25253 (10/21/2009)

---

Phil did...

I just expanded my testing to include cells populated via drop-down and these worked fine too. So there must be something more to this ...

bitbucket-25253 (10/21/2009)

---

1. Check those cells to see if they contain a formula

If they do then

2. Check Excel help for "Replace a formula with its result"

Unfortunately the only...

That's too complex for anything but the Script Component - I suggest you concentrate your efforts there.

Except for your post, the word 'wizard' appears nowhere in this thread.

Siddharth Mehta (10/21/2009)

---

Not just Union All, there are other components that would have the same issue in case of duplicate column names. Its a better practice to keep column names...

Siddharth Mehta (10/21/2009)

---

Step 1: copy the entire data from your spreadsheet

Step 2: Use the option of Edit Top 200 rows by right-clicking the table in SSMS

Step 3: Paste the data,...

I suspect that you need to use SQL syntax which is supported on DB2.

Check here and search for INSERT as a possible resource to get you through this.