Substring? - 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 2008 Forums

Transact-SQL (2008)

Substring?

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-12 : 21:30:55

I am using SQL 2008 to query a table in a database that includes a field that is formatted as nvarchar and appears as A700003.

I would like this to appear in my results as A7 part 3. There are instances where the desired result will not have a part. I have seen the substring command to do something similar, but am not sure if that is the correct command. Will that work? If so, how do you use the command?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 03:31:14

SELECT SUBSTRING(Col1, 1, 2) + ' part ' + CAST(CAST(SUBSTRING(Col1, 3, 5) AS INT) AS VARCHAR(11))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-13 : 06:02:07

quote:
Originally posted by Peso

SELECT SUBSTRING(Col1, 1, 2) + ' part ' + CAST(CAST(SUBSTRING(Col1, 3, 5) AS INT) AS VARCHAR(11))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

Thanks, Peso. I will try this now and let you know how it works!

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-13 : 06:07:15

quote:
Originally posted by jcb267

quote:
Originally posted by Peso

SELECT SUBSTRING(Col1, 1, 2) + ' part ' + CAST(CAST(SUBSTRING(Col1, 3, 5) AS INT) AS VARCHAR(11))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

Thanks, Peso. I will try this now and let you know how it works!

I am trying to get one of those cases that have no "part" to work. This is what I have:

select substring([2552].[wksht],1,2) as WKSHT, [2552]].
[wksht] [2552].[line], [2552].[col], [2552].[value], [2552].[mcr_no], [hpi].[Hosp_Name],
[hpi].[Street_Addr], [hpi].[City], [hpi].[State], [hpi].[Zip_Code], [2552].[fy_e], [2552].[fi_n],
[2552].[FI_RCPT_DT],[hpi].[Urban1_Rural2]

from [2552] left join [hpi]
on [2552].[mcr_no] = [hpi].[PROVIDER_NUMBER]

but keep getting this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

I am not sure what it means. Can you help me with that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 06:11:26

[code]SELECT substring([2552].[wksht], 1, 2) as WKSHT,
[2552].[wksht],
[2552].[line],
[2552].[col],
[2552].[value],
[2552].[mcr_no],
[hpi].[Hosp_Name],
[hpi].[Street_Addr],
[hpi].[City],
[hpi].[State],
[hpi].[Zip_Code],
[2552].[fy_e],
[2552].[fi_n],
[2552].[FI_RCPT_DT],
[hpi].[Urban1_Rural2]
from [2552]
LEFT JOIN [hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no][/code]

E 12°55'05.63"
N 56°04'39.26"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-13 : 06:35:17

quote:

Originally posted by Peso

SELECT		substring([2552].[wksht], 1, 2) as WKSHT,
		[2552].[wksht],
		[2552].[line],
		[2552].[col],
		[2552].[value],
		[2552].[mcr_no],
		[hpi].[Hosp_Name],
		[hpi].[Street_Addr],
		[hpi].[City],
		[hpi].[State],
		[hpi].[Zip_Code],
		[2552].[fy_e],
		[2552].[fi_n],
		[2552].[FI_RCPT_DT],
		[hpi].[Urban1_Rural2]
from		[2552]
LEFT JOIN	[hpi] on [hpi].[PROVIDER_NUMBER] = [2552].[mcr_no]

E 12°55'05.63"
N 56°04'39.26"

Thank you, Peso. That worked just fine! What was wrong with what I had before?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 06:41:08

Two errors in "[2552]].[wksht] [2552].[line],"

1. An extra bracket
2. No comma between column names

E 12°55'05.63"
N 56°04'39.26"

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-13 : 09:36:31

quote:
Originally posted by Peso

Two errors in "[2552]].[wksht] [2552].[line],"

1. An extra bracket
2. No comma between column names

E 12°55'05.63"
N 56°04'39.26"

Thank you very much! It is hard to pick out errors, I am getting better at it though......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources