Sql issue

Question

Sql issue

jman76

Hall of Fame

Points: 3388
More actions
November 29, 2013 at 4:25 am

#283039

i have a column called: CustomerName in SQL Server:
I am trying to remove the *NA in any customer field. However some customer fields dont have the star.
For example:
West Middlesex Hospital *LN
Norfolk & Norwich University Hospital*EA
West Suffolk NHS Trust *EA
I have used this code:
Select RTRIM(LEFT('CustomerName',CHARINDEX('*','CustomerName')-1))
SELECT CASE WHEN CHARINDEX('*','CustomerName') < 1 THEN
RTRIM('CustomerName') ELSE
RTRIM(LEFT('CustomerName',CHARINDEX('*','CustomerName')-1))END
I appear to be getting the message:
Invalid length parameter passed to the left function.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Dohsan SSCrazy Points: 2972 More actions · Answer 1

You're passing a string as an argument rather than the column name. Remove the Apostophes i.e. CustomerName instead of 'CustomerName'

richardbriansmith-814479 SSC Enthusiast Points: 115 More actions · Answer 2

Personally I would flip the string, use substring to remove the last 3 characters, then flip it back. eg select reverse(substring(reverse(CustomerName),4,200))

sestell1 SSChampion Points: 10230 More actions · Answer 3

How about this:

SELECT REPLACE(CustomerName,'*NA','')

That will remove all instances of '*NA' within CustomerName.

Would that work for you, or is there a chance that the string '*NA' will show up within the string somewhere were you don't want to remove it?

ScottPletcher SSC Guru Points: 101105 More actions · Answer 4

SELECT RTRIM(LEFT(CustomerName,CHARINDEX('*',CustomerName + '*')-1))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".