SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Creating Substring with a vayring end point
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JohnnyMen
Starting Member

USA
3 Posts

Posted - 07/05/2013 :  16:54:49  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
403 Posts

Posted - 07/06/2013 :  00:33:40  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/07/2013 :  10:34:04  Show Profile  Reply with Quote
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
Go to Top of Page

JohnnyMen
Starting Member

USA
3 Posts

Posted - 07/08/2013 :  08:24:20  Show Profile  Reply with Quote
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


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 07/08/2013 :  08:30:01  Show Profile  Reply with Quote
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.

Edited by - James K on 07/08/2013 08:31:41
Go to Top of Page

JohnnyMen
Starting Member

USA
3 Posts

Posted - 07/08/2013 :  12:51:15  Show Profile  Reply with Quote
Thanks everyone!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000