Remove Unwanted Char From Record Details

Question

Post reply

Remove Unwanted Char From Record Details

ubeauty

Ten Centuries

Points: 1034
More actions
April 21, 2009 at 8:17 am

#203196

Hi all
I'm trying to remove unwanted characters which i'm assuming are tab characters from a records details.
As you can see to the right of the client code is white space up to where i've typed end to hopefully illustrate the problem.
LC157790to the left of this is white space
I've tried using the following code but to no avail as it keeps returning (0 rows affected)
IF EXISTS(SELECT COUNT(*)
FROM vpmser.dbo.clientsupplier
WHERE clientcode like 'LC157790%'
having COUNT(*)=1)
Begin
declare@orgclientcode varchar(10)
declare@newclientcode varchar(10)
select@orgclientcode = clientcode
fromvpmser.dbo.clientsupplier
whereclientcode like 'LC157790%'
set@newclientcode = 'LCERROR'
updatevpmser.dbo.clientsupplier
setvpmser.dbo.clientsupplier.clientcode=@newclientcode
where vpmser.dbo.clientsupplier.clientcode=@orgclientcode
We have unique client codes so that is why i'm tyring to amend to another value first then i'll be changing it back.
Any pointers in the right direction would be much appreciated.
Thanks in advance
Karl

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

If the white space is to the left, why aren't you searching where clientcode like

%LC157790%

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

ubeauty Ten Centuries Points: 1034 More actions · Answer 2

Aplogies for the confusion, the white space is to the right of the code

So it looks something like LC157790[][][]

When i tried to copy and paste this onto the forum question it didn't quite appear correctly

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi

If you are sure that the trailing "spaces" are tabs you can use this statement to remove them:

UPDATE clientsupplier

SET clientcode = LEFT(clientcode, CHARINDEX(CHAR(9), clientcode, 1)) - 1

WHERE CHARINDEX(CHAR(9), clientcode, 1) != 0

The CHAR(9) represents the tab character and the CHARINDEX searches the occurring position of the tab within your clientcode

Greets

Flo

ubeauty Ten Centuries Points: 1034 More actions · Answer 4

It doesn't look like they are tab characters as when running the script above the following error message gets returned:

Conversion failed when converting the varchar value 'LC157790' to data type int.

Do i need to add some kind of CAST statement beforehand?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

ubeauty (4/22/2009)
It doesn't look like they are tab characters as when running the script above the following error message gets returned:
Conversion failed when converting the varchar value 'LC157790' to data type int.
Do i need to add some kind of CAST statement beforehand?

DROP TABLE #clientsupplier

CREATE TABLE #clientsupplier (clientcode VARCHAR(20))

INSERT INTO #clientsupplier (clientcode)

SELECT 'LC157790' UNION ALL

SELECT 'LC157790' + CHAR(9) UNION ALL

SELECT 'LC157790' + CHAR(10) UNION ALL

SELECT 'LC157790' + CHAR(13)

SELECT '[' + clientcode + ']' FROM #clientsupplier

UPDATE #clientsupplier

SET clientcode = LEFT(clientcode, CHARINDEX(CHAR(9), clientcode, 1)- 1)

WHERE CHARINDEX(CHAR(9), clientcode, 1) != 0

SELECT '[' + clientcode + ']' FROM #clientsupplier

Nah, it's a small error in Flo's code - there's a first time for everything!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ubeauty Ten Centuries Points: 1034 More actions · Answer 6

I won't be able to drop the table due to keys in place (1 primary and 13 foreign) but when using the update section of the code above:

UPDATE clientsupplier

SET clientcode = LEFT(clientcode, CHARINDEX(CHAR(9), clientcode, 1)- 1)

WHERE CHARINDEX(CHAR(9), clientcode, 1) != 0

It returns the following error message:

Incorrect syntax near '<'.

ubeauty Ten Centuries Points: 1034 More actions · Answer 7

ubeauty

Ten Centuries

Points: 1034

April 22, 2009 at 2:12 am

#981992

Forget the HTML tags!! 🙂

ubeauty Ten Centuries Points: 1034 More actions · Answer 8

Many thanks for the responses their was a problem with the placement with the brackets but once moved this corrected the problem and the client code updated ok and now appears correctly as it should. 😀

UPDATE clientsupplier

SET clientcode = LEFT(clientcode, CHARINDEX(CHAR(9), clientcode, 1) - 1)

WHERE CHARINDEX(CHAR(9), clientcode, 1) != 0

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

ubeauty (4/22/2009)
I won't be able to drop the table due to keys in place (1 primary and 13 foreign) but when using the update section of the code above:
UPDATE clientsupplier
SET clientcode = LEFT(clientcode, CHARINDEX(CHAR(9), clientcode, 1)- 1)
WHERE CHARINDEX(CHAR(9), clientcode, 1) != 0
It returns the following error message:
Incorrect syntax near '<'.

Darn it, sorry about that - it was bold in a code window grrr!

This - #clientsupplier - is a temporary table holding sample data against which code can be tested. It's not part of the solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ubeauty Ten Centuries Points: 1034 More actions · Answer 10

Thanks for the info I thought you were asking me to drop and recreate the table, and it was only after i ran the script did i notice the tags!!

Now i need to use the same logic to update the assignments that are tied to this client which begin with the client code i.e LC157790/TCP

As you can see the tab chars in the assignment name before the forward slash and assignment code TCP.

This is what i normally use:

UPDATEAssignment

SETCode = REPLACE(Code ,SUBSTRING(Code ,1,LEN(Code )-(LEN(CodeRIGHT)+1)),ClientCode)

FROMAssignment INNER JOIN Client ON Client.ClientID = Assignment.ClientID

WHERELEFT(Assignment.Code ,LEN(ClientCode )) ClientCode

AND Client.ClientCode = 'LC157790'

This is returning (0 row(s) affected) even though i know for a fact that there is one.

Not sure where i need to insert the search for using the CHARINDEX function.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

So there are embedded tab chars in strings which look like this: 'LC157790 /TCP'

In which case Flo's function will truncate from the first tab found...

First things first - do you need those tabs there for anything? If not, they could be replaced with, say, spaces (substituted for # in the example for visibility)

DROP TABLE #clientsupplier

CREATE TABLE #clientsupplier (clientcode VARCHAR(20))

INSERT INTO #clientsupplier (clientcode)

SELECT 'LC157790' UNION ALL

SELECT 'LC157790' + CHAR(9) UNION ALL

SELECT 'LC157790' + CHAR(10) UNION ALL

SELECT 'LC157790' + CHAR(13)

SELECT '[' + clientcode + ']' FROM #clientsupplier

UPDATE #clientsupplier

SET clientcode = REPLACE(clientcode, CHAR(9), '#')

WHERE CHARINDEX(CHAR(9), clientcode, 1) != 0

SELECT '[' + clientcode + ']' FROM #clientsupplier

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ubeauty Ten Centuries Points: 1034 More actions · Answer 12

No the tabs are not needed as it's a user input error copying from a spreadsheet i should imagine.

Whenever a client code is amended the second script is then run to update the assignment code (which is made up of the client code followed by a forward slash and then the assignment code)

The script to update the assignment code with the newly updated LC157790 without the tab chars is just not working

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

ubeauty (4/22/2009)
The script to update the assignment code with the newly updated LC157790 without the tab chars is just not working

Have you tested the SELECT part on its own?

SELECT a.*, c.*,

REPLACE(Code, SUBSTRING(Code, 1, LEN(Code) - (LEN(CodeRIGHT) + 1)), ClientCode)

FROM Assignment a

INNER JOIN Client c ON c.ClientID = a.ClientID

WHERE LEFT(a.Code, LEN(c.ClientCode )) c.ClientCode

AND c.ClientCode = 'LC157790'

It would be handy if you could set up some table create statements for Assignment and Client, with INSERTs to populate them with some sample data. Then we'd all have something to code and test against. You don't need to script out all of the columns, just those which are relevant.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 14

Chris Morris (4/22/2009)
Nah, it's a small error in Flo's code - there's a first time for everything!

Heh... Thanks Chris 🙂 but not first time. I should stop tryin' to write statements on the fly without testing!

Greets

Flo