Functions - SQL Server Forums

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)

Functions

Author

Topic

dyanm74
Starting Member

5 Posts

Posted - 2013-04-18 : 15:17:41

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

1974 Posts

Posted - 2013-04-18 : 15:24:19

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-18 : 16:22:51

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.

dyanm74
Starting Member

5 Posts

Posted - 2013-04-22 : 09:47:09

I would just need to extract the state

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-22 : 13:49:16

[code]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)[/code]

dyanm74
Starting Member

5 Posts

Posted - 2013-04-23 : 09:20:13

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-23 : 12:03:54

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

dyanm74
Starting Member

5 Posts

Posted - 2013-04-23 : 12:24:12

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.

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-23 : 13:38:06

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

dyanm74
Starting Member

5 Posts

Posted - 2013-04-24 : 08:59:57

Sorry about that. Thanks so much for your help. This works great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources