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)
 Functions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dyanm74
Starting Member

USA
5 Posts

Posted - 04/18/2013 :  15:17:41  Show Profile  Reply with Quote
Is there a way to determine the length of a string within a string?

For example:
Alert Message varchar (255) contains the following message:
"Emergency license for John Smith in the state of CA is active for 90 days."

John Smith is variable in size.

I need do a data roll to take the state (in this example is CA) and create a new state field with it.

So I need to determine how long "Emergency license for John Smith in the state of " is when the name length can change.

I hope I explained this clearly. Thanks in advance.

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/18/2013 :  15:24:19  Show Profile  Visit chadmat's Homepage  Reply with Quote
Subtract the length of the known string from the length of the string with the name, and that should give you the length of the name.

-Chad
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/18/2013 :  16:22:51  Show Profile  Reply with Quote
Maybe if you can show sample data and expected output. In that sample you showed above what do you want to exract from the string? Just the state, in this case CA?

If it is more complicated than that, we are going to need more info. If that is all you need to do, then it's relativly simple to prase the string.

Edited by - Lamprey on 04/18/2013 16:23:21
Go to Top of Page

dyanm74
Starting Member

USA
5 Posts

Posted - 04/22/2013 :  09:47:09  Show Profile  Reply with Quote
I would just need to extract the state
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/22/2013 :  13:49:16  Show Profile  Reply with Quote
DECLARE @Message VARCHAR(100) = 'Emergency license for John Smith in the state of CA is active for 90 days';

SELECT SUBSTRING(@Message, CHARINDEX('in the state of', @Message) + 16, 2)
Go to Top of Page

dyanm74
Starting Member

USA
5 Posts

Posted - 04/23/2013 :  09:20:13  Show Profile  Reply with Quote
Thank you. This works great if the name will always be John Smith. But that changes. So I need to find out the length of the name. It's not a separate variable. It's embedded in the message.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/23/2013 :  12:03:54  Show Profile  Reply with Quote
quote:
Originally posted by dyanm74

Thank you. This works great if the name will always be John Smith. But that changes. So I need to find out the length of the name. It's not a separate variable. It's embedded in the message.

I don't understand what the name has to do with it.
DECLARE @Foo TABLE (Message VARCHAR(100))

INSERT @Foo
VALUES
('Emergency license for John Smith in the state of CA is active for 90 days'),
('Emergency license for Johnny Smith in the state of WA is active for 90 days'),
('Emergency license for Sally Johnson in the state of NY is active for 90 days'),
('Emergency license for Benjamín Santiago in the state of AZ is active for 90 days'),
('Emergency license for Liam Alexander in the state of TX is active for 90 days')


SELECT SUBSTRING(Message, CHARINDEX('in the state of', Message) + 16, 2)
FROM @Foo

(5 row(s) affected)

----
CA
WA
NY
AZ
TX
Go to Top of Page

dyanm74
Starting Member

USA
5 Posts

Posted - 04/23/2013 :  12:24:12  Show Profile  Reply with Quote
Sorry I am not being specific. The field alert_message already exists with the data:
'Emergency license for John Smith in the state of CA is active for 90 days'),
('Emergency license for Johnny Smith in the state of WA is active for 90 days'),
('Emergency license for Sally Johnson in the state of NY is active for 90 days'),
('Emergency license for Benjamín Santiago in the state of AZ is active for 90 days'),
('Emergency license for Liam Alexander in the state of TX is active for 90 days'

I need to extract the state for reporting purposes.
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 04/23/2013 :  13:38:06  Show Profile  Visit chadmat's Homepage  Reply with Quote
quote:
Originally posted by dyanm74

Sorry I am not being specific. The field alert_message already exists with the data:
'Emergency license for John Smith in the state of CA is active for 90 days'),
('Emergency license for Johnny Smith in the state of WA is active for 90 days'),
('Emergency license for Sally Johnson in the state of NY is active for 90 days'),
('Emergency license for Benjamín Santiago in the state of AZ is active for 90 days'),
('Emergency license for Liam Alexander in the state of TX is active for 90 days'

I need to extract the state for reporting purposes.



Lamprey has given you the code to do that.

-Chad
Go to Top of Page

dyanm74
Starting Member

USA
5 Posts

Posted - 04/24/2013 :  08:59:57  Show Profile  Reply with Quote
Sorry about that. Thanks so much for your help. This works great!
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.17 seconds. Powered By: Snitz Forums 2000