Creating Substring with a vayring end point

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Creating Substring with a vayring end point

Author

Topic

JohnnyMen
Starting Member

3 Posts

Posted - 2013-07-05 : 16:54:49

Hello all,

I need to write a query in which I'll take a substring of value. The trick is the end point of the substring will vary between the second or third character depending on where the final alpha character is. I need the final character of the substring to be the final alpha character. There will always be either 2 or 3 alpha characters. I'm not sure if there is a way to write a case statement to accommodate this.

Example 1 - AA######L: will be substring(<TableName> 1 for 2)"} <Variable>

Example 2 - AAA#####: will be substring(<TableName> 1 for 3)"} <Variable>

Please let me know if more information is needed.

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-07-06 : 00:33:40

Hi,

Yes , you can with a case stmt.



declare @var as varchar(50) ='AA######L'
--set @var ='AAA#####'

SELECT @var 
	, SUBSTRING(@var,1, CASE WHEN  RIGHT(@var,1) LIKE '[^a-Z]' THEN 3
								ELSE 2 END )
    ,CASE WHEN  RIGHT(@var,1) LIKE '[^a-Z]' THEN 'non alpha'
	 ELSE  'alpha'
	 END

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-07 : 10:34:04

if you're sure that its always # characters, wont this be enough?

SELECT REPLACE(column,'#','')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

JohnnyMen
Starting Member

3 Posts

Posted - 2013-07-08 : 08:24:20

Good point, but the numeric characters will vary greatly. Won't the syntax below literally look for the '#' to replace?

quote:
Originally posted by visakh16

if you're sure that its always # characters, wont this be enough?

SELECT REPLACE(column,'#','')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-08 : 08:30:01

quote:
Originally posted by JohnnyMen

Good point, but the numeric characters will vary greatly. Won't the syntax below literally look for the '#' to replace?
quote:
Originally posted by visakh16

if you're sure that its always # characters, wont this be enough?

SELECT REPLACE(column,'#','')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Yes, it will look for the character # rather than numbers. It T-SQL, # is not a place holder or indicator for digits (or for anything else).

Don't use that unless you are looking specifically for the #character. Instead, use stepson's suggestion and adapt it to your needs.

JohnnyMen
Starting Member

3 Posts

Posted - 2013-07-08 : 12:51:15

Thanks everyone!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources