Unoffical empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

Topic Options
#373322 - 07/06/2021 16:50 SQL Help
tahir
pooh-bah

Registered: 27/02/2004
Posts: 1913
Loc: London
In T-SQL how do I trim all characters after a character? I have a mixed bag of Style references to process, all different lengths, some with a 5 character suffix after '.', some with 4 character suffix after '.' and others with no suffix at all. So if my raw data was:

FJ210001.D023
FJ210002.D023S
LJA002

I'd like the results to be shown as:

FJ210001
FJ210002
LJA002

Any help appreciated

Top
#373323 - 07/06/2021 18:05 Re: SQL Help [Re: tahir]
andy
carpal tunnel

Registered: 10/06/1999
Posts: 5916
Loc: Wivenhoe, Essex, UK
You should be able to use CHARINDEX and LEFT together to get what you need.

https://stackoverflow.com/questions/40924008/how-to-use-substring-and-patindex-in-sql-server
_________________________
Remind me to change my signature to something more interesting someday

Top
#373325 - 08/06/2021 11:06 Re: SQL Help [Re: tahir]
tahir
pooh-bah

Registered: 27/02/2004
Posts: 1913
Loc: London
Thanks, I'd never even heard of the function. Took me a while to figure out why I was getting "Invalid length parameter passed to the LEFT or SUBSTRING function" but all sorted now.

Top
#373329 - 08/06/2021 11:59 Re: SQL Help [Re: tahir]
andy
carpal tunnel

Registered: 10/06/1999
Posts: 5916
Loc: Wivenhoe, Essex, UK
I've not done any T-SQL in many years, so that was all thanks to Google and StackOverflow wink
_________________________
Remind me to change my signature to something more interesting someday

Top
#373330 - 08/06/2021 12:55 Re: SQL Help [Re: tahir]
tahir
pooh-bah

Registered: 27/02/2004
Posts: 1913
Loc: London
I was searching for SUBSTRING and TRIM, so I guess wouldn't have found those smile

Top
#373332 - 08/06/2021 14:05 Re: SQL Help [Re: tahir]
andy
carpal tunnel

Registered: 10/06/1999
Posts: 5916
Loc: Wivenhoe, Essex, UK
I think I searched for "transact sql regex" and found CHARINDEX instead.
_________________________
Remind me to change my signature to something more interesting someday

Top
#373334 - 09/06/2021 11:48 Re: SQL Help [Re: tahir]
tahir
pooh-bah

Registered: 27/02/2004
Posts: 1913
Loc: London
Yeah, did think regex would help, just wasn't sure how it would work with sql.

Top