String Help - LTRIM ?

Question

Post reply

String Help - LTRIM ?

clearwater

Newbie

Points: 8
More actions
May 27, 2010 at 5:37 pm

#94912

I need some help. I have a varchar field called FIRSTNAME in a table called CUSTOMER. Some of the entries for FIRSTNAME have preceding symbols to the left before the alpha characters.
I realize this data is not modeled correctly. Someone used the symbols of -,*,<,<<,<<<,$ for internal use coding.
These are the possible non-alpha combinations that I know of...
How can I do a SELECT statement to only return the alpha characters? So only "Charlie" returns...
-Charlie
*Charlie
<Charlie
<<Charlie
<<<Charlie
$Charlie

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

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

Aparna-1 SSCrazy Points: 2339 More actions · Answer 1

Hi,

Hope this helps you.

DECLARE @Temp Table

(

FirstName VARCHAR(100)

)

INSERT INTO @Temp

SELECT '-Charlie'

UNION ALL

SELECT '*Charlie'

UNION ALL

SELECT '<Charlie'

UNION ALL

SELECT '<<Charlie'

UNION ALL

SELECT '<<<Charlie'

UNION ALL

SELECT '$Charlie'

UNION ALL

SELECT 'Charlie'

SELECT SUBSTRING(FirstName, PATINDEX('%[A-Z ,a-z]%' , FirstName), LEN(FirstName)) AS FirstName

FROM

@Temp

clearwater Newbie Points: 8 More actions · Answer 2

Hi thank you for the ideas.

I need a little modification...

The code you gave me works great if the symbols or non alpha characters are on the left side of the FirstName. I just found out that I also have some non alpha characters on the right side of FirstName.

SELECT SUBSTRING(FirstName, PATINDEX('%[A-Z ,a-z]%' , FirstName), LEN(FirstName)) AS FirstName

Is there away to use PATINDEX to get "Charlie" from any of the situations below?

-Charlie

*Charlie

<Charlie

<<Charlie

<<<Charlie

$Charlie

Charlie-

Charlie*

Charlie<

Charlie<<

Charlie<<<

Charlie$

advance thank you!

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

How about writing a function as;

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Atif Sheikh

-- Create date: 28-05-2010

-- Description:Trim Non Alpha Characters

-- =============================================

ALTER FUNCTION fnTrimNonAlphaCharacters

(

@pString varchar(max)

)

RETURNS varchar(max)

AS

BEGIN

Declare @vTable Table (Ch Char(1))

Declare @vRetString varchar(max)

Insert Into @vTable

SELECT SUBSTRING(@pString,N,1)

FROM dbo.tblTally a

WHERE N <= LEN(@pString) ORDER BY N

Set @vRetString = ''

Select @vRetString = @vRetString + Ch

from @vTable

Where Ascii(Ch) between 97 and 122

Or Ascii(Ch) between 65 and 90

Or Ascii(Ch) = 32

Return @vRetString

END

GO

I hope it will help you.

And then Use it as;

DECLARE @Temp Table

(

FirstName VARCHAR(100)

)

INSERT INTO @Temp

SELECT '-Charlie'

UNION ALL

SELECT '*Charlie'

UNION ALL

SELECT '<Charlie098098098'

UNION ALL

SELECT '<<Charlie'

UNION ALL

SELECT '<<<Charlie*****'

UNION ALL

SELECT '$Charlie>>'

UNION ALL

SELECT 'Charlie>>>>>'

Select dbo.fnTrimNonAlphaCharacters(FirstName)

from @Temp

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Atif Sheikh (5/28/2010)
How about writing a function as;
[/code]

Although it's tempting to use such a thing, be very aware that the use of such scalar functions will slow your code down... a lot!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Try looking here:

http://www.sqlservercentral.com/Forums/Topic695508-338-1.aspx

Regex (regular expressions) are likely going to be your best bet to take this one if you have "junk" all over your input column. On short columns however, the tally table will likely outperform any CLR you toss at it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?